package com.eorchis.module.trainingclass.dao.impl;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;
import com.eorchis.constants.DeptConstants;
import com.eorchis.constants.RoleConstants;
import com.eorchis.constants.SystemConstants;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.entity.impl.AbstractBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.IQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.basedao.query.mapper.MapperType;
import com.eorchis.core.basedao.query.mapper.ResultMapperFactory;
import com.eorchis.core.basedao.query.mapper.impl.BeanResultMapper;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.TrainingConstants;
import com.eorchis.module.classuser.domain.ClassUser;
import com.eorchis.module.classuser.domain.DepartmentUserBean;
import com.eorchis.module.department.domain.Department;
import com.eorchis.module.department.domain.DepartmentTreeCondition;
import com.eorchis.module.department.ui.commond.DepartmentValidCommond;
import com.eorchis.module.trainingclass.dao.ITrainingClassDao;
import com.eorchis.module.trainingclass.domain.BaseAttachment;
import com.eorchis.module.trainingclass.domain.BaseUserBean;
import com.eorchis.module.trainingclass.domain.SyncTrainingClassQueryBean;
import com.eorchis.module.trainingclass.domain.TrainingClass;
import com.eorchis.module.trainingclass.domain.TrainingClassPartFieldUpdateBean;
import com.eorchis.module.trainingclass.domain.TrainingClassQueryCondition;
import com.eorchis.module.trainingclass.domain.UserIdAndYear;
import com.eorchis.module.trainingclass.domain.UserInfoBean;
import com.eorchis.module.trainingclass.querybean.TrainingClassQueryBean;
import com.eorchis.module.trainingclass.ui.commond.TrainingClassQueryCommond;
import com.eorchis.module.trainingclass.ui.commond.TrainingClassValidCommond;
import com.eorchis.module.trainingquestionnaire.domain.QuestionnaireEntity;
import com.eorchis.module.user.domain.User;
import com.eorchis.module.util.SqlQueryUtil;
import com.eorchis.utils.DateUtil;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: TrainingClassDaoImpl<br>
 * Description: 培训班信息数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * @author Max
 * @createDate 2013-12-10
 * @version $Revision$
 */
@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.trainingclass.dao.impl.TrainingClassDaoImpl")
public class TrainingClassDaoImpl
extends HibernateAbstractBaseDao
implements ITrainingClassDao {

	public Class<? extends IBaseEntity> entityClass() {
		return TrainingClass.class;
	}

	public void queryConditionProcessor(
			IConditionBuilder defaultQueryConditionBuilder,
			IQueryCommond queryCommond) {
		TrainingClassQueryCommond trainingClassQueryCommond = (TrainingClassQueryCommond)queryCommond;

		defaultQueryConditionBuilder.setBaseQueryString("SELECT t FROM TrainingClass t");
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchClassIds())) {
			defaultQueryConditionBuilder.addCondition("t.classId", CompareType.IN, trainingClassQueryCommond.getSearchClassIds());
		}
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchClassId())) {
			defaultQueryConditionBuilder.addCondition("t.classId", CompareType.EQUAL,trainingClassQueryCommond.getSearchClassId());
		}

		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchUpdateClassIds())) {
			defaultQueryConditionBuilder.addCondition("t.classId", CompareType.IN, trainingClassQueryCommond.getSearchUpdateClassIds());
		}
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchClassName())) {
			defaultQueryConditionBuilder.addCondition("t.classMainName", CompareType.EQUAL, trainingClassQueryCommond.getSearchClassName());
		}
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchClassType())) {
			defaultQueryConditionBuilder.addCondition("t.classType.dataCode", CompareType.EQUAL, trainingClassQueryCommond.getSearchClassType());
		}	
		//活动状态
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchActiveState())) {
			defaultQueryConditionBuilder.addCondition("t.activeState", CompareType.EQUAL, trainingClassQueryCommond.getSearchActiveState());
		}
		//审核状态
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchAuditState())) {
			defaultQueryConditionBuilder.addCondition("t.auditState", CompareType.EQUAL, trainingClassQueryCommond.getSearchAuditState());
		}
		//发布状态
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchPublishState())) {
			defaultQueryConditionBuilder.addCondition("t.publishState", CompareType.EQUAL, trainingClassQueryCommond.getSearchPublishState());
		}
		//班级编号
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchClassNum())) {
			defaultQueryConditionBuilder.addCondition("t.classNum", CompareType.EQUAL, trainingClassQueryCommond.getSearchClassNum());
		}
		//班级的报名开始时间
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchEnterBeginTimeDate())) {
			defaultQueryConditionBuilder.addCondition("t.enterBeginTime", CompareType.LESS_THAN_OR_EQUAL, trainingClassQueryCommond.getSearchEnterBeginTimeDate());
		}
		//班级的报名结束时间
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchEnterEndTimeDate())) {
			defaultQueryConditionBuilder.addCondition("t.enterEndTime", CompareType.GREATER_THAN_OR_EQUAL, trainingClassQueryCommond.getSearchEnterEndTimeDate());
		}
		//承办部委ID
		if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchMinistrieID())) {
			defaultQueryConditionBuilder.addCondition("t.ministrieID", CompareType.EQUAL, trainingClassQueryCommond.getSearchMinistrieID());
		}
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);

	}

	/**
	 * 查询班级列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2014-12-10
	 */
	@Override
	public List<TrainingClassQueryBean> findTrainingClassList(
			TrainingClassQueryCommond queryCommond) throws Exception {
		StringBuffer queryHql = new StringBuffer();
		//拼写查询的sql
		StringBuffer hql = new StringBuffer();

		hql.append("select distinct t.classId as classId, t.createDate as createDate, nvl(t.assessStartTime,t.endDate-1) as assessStartTime,nvl(t.assessEndTime,t.endDate+2) as assessEndTime, t.trainingDeptName as trainingDeptName,t.reportState as reportState,t.auditState as auditState," +
				"t.openState as openState, t.className as className, t.classNum as classNum,t.enterBeginTime as enterBeginTime,t.enterEndTime as enterEndTime," +
				"t.beginDate as beginDate,t.endDate as endDate,t.trainingHour as trainingHour,t.signUpState as signUpState,t.selectedLetters as selectedLetters,");
//		hql.append(" (select nvl(sum(cp.planCount),0) from ClassPlanApply cp where cp.classId=t.classId) as planCount,");//计划人数
		hql.append(" (select nvl(sum(cp.asignCount),0) from ClassPlanApply cp where cp.classId=t.classId) as kbCount,");//可报名额 add by NingSS at 20160217
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5)) as reportedNum,");//已报人数,审核通过人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId) as reportedNumber,");//已报人数,包括审核通过、审核未通过、未审核
		hql.append(" t.publishState as publishState,t.activeState as activeState,ct.dataCode as classTypeCode ,ct.dataName as classTypeStr,t.perfectState as perfectState,t.allotDeptState as allotDeptState,");
		hql.append("t.sponsorID as sponsorID,t.sponsorName as sponsorName ");//主办单位部委id和名称
//		if (PropertyUtil.objectNotEmpty(queryCommond.getDeptId()))
//		hql.append(",(select (case when count(dept.deptID) > 0 then 'y' else 'n' end) from Department dept where dept.deptID=t.ministrieID and dept.treepath like :scope) as canBeAudited "); // 是否可进行报名审核 y/n
		hql.append(" FROM TrainingClass t left join t.classType ct left join t.applyWay aw left join t.scopeDepartment sd,Department d  where 1=1 and  d.deptID=t.sponsorID ");
		//查询数量的sql	
		StringBuffer countHql = new StringBuffer("FROM TrainingClass t left join t.classType ct left join t.applyWay aw left join t.scopeDepartment sd,Department d where 1=1  and  d.deptID=t.sponsorID ");

		if(DeptConstants.ZDBMJG.equals(queryCommond.getSearchClassTrainingType())){
			hql.append(" and t.publishState = 1 ");
			countHql.append(" and t.publishState = 1 ");
		}
		Object[] queryObj = buildQueryObject(queryCommond, queryHql);
		queryHql = (StringBuffer)queryObj[0];
		Map<String,Object> values = (Map<String,Object>)queryObj[1];
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		countHql.append(queryHql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
//		Long count = this.count(queryCondition);

		// 数据列表查询
		hql.append(queryHql);

		//拼写排序的sql begin
		
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			hql.append(" order by t.createDate desc");
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		List<TrainingClassQueryBean> resultList=new ArrayList<TrainingClassQueryBean>();
		if(queryCommond.getLimit()==-1){
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition1);
		}else {
			int firstResult = queryCommond.calculate(this.findList(queryCondition1).size());
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition1,firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	
	/**
	 * 查询班级列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2014-12-10
	 */
	@Override
	public List<TrainingClassQueryBean> findTrainingClassList2( TrainingClassQueryCommond queryCommond) throws Exception {
		StringBuffer hql = new StringBuffer();
		hql.append(" SELECT DISTINCT tc.CLASS_ID AS \"classId\", tc.CREATE_DATE AS \"createDate\", tc.class_user_num \"classUserNum\", "
				+ "STR_TO_DATE(IFNULL ( tc.ASSESS_START_TIME, tc.END_DATE - 1 ),'%Y-%m-%d') AS \"assessStartTime\", "
				+ "STR_TO_DATE(IFNULL ( tc.ASSESS_END_TIME, tc.END_DATE + 2 ),'%Y-%m-%d') AS \"assessEndTime\", "
				+ "tc.TRAINING_DEPT_NAME AS \"trainingDeptName\",  IFNULL (tcount.c1,0) \"reportedNumber\", case qso.questionnaire_id when null then 2 else 1 "
				+ "end \"configQuestionnaireState\", IFNULL(tcount.c2, 0) \"reportedNum\", tc.category_Id AS \"categoryId\", tc.beoff_Station AS \"beoffStation\", "
				+ "tc.classname_Year AS \"classnameYear\", tc.REPORT_STATE AS \"reportState\", tc.AUDIT_STATE AS \"auditState\", tc.OPEN_STATE AS \"openState\", "
				+ "tc.CLASS_NAME AS \"className\", tc.CLASS_NUM AS \"classNum\", IFNULL (tc.lock_state, 2) AS \"lockState\", tc.ENTER_BEGIN_TIME AS \"enterBeginTime\", "
				+ "tc.ENTER_END_TIME AS \"enterEndTime\", tc.BEGIN_DATE AS \"beginDate\", tc.END_DATE AS \"endDate\", tc.TRAINING_HOUR AS \"trainingHour\", "
				+ "tc.SIGNUP_STATE AS \"signUpState\", tc.selected_letters AS \"selectedLetters\", tc.PUBLISH_STATE AS \"publishState\", tc.ACTIVE_STATE AS \"activeState\", "
				+ "bb1.DATA_CODE AS \"classTypeCode\", bb1.DATA_NAME AS \"classTypeStr\", tc.PERFECT_STATE AS \"perfectState\", tc.ALLOTDEPT_STATE AS \"allotDeptState\", "
				+ "tc.SPONSOR_ID AS \"sponsorID\", tc.SPONSOR_NAME AS \"sponsorName\" FROM TRAINING_CLASS tc left join BASE_DEPARTMENT bd on bd.DEPID = tc.SPONSOR_ID "
				+ "LEFT JOIN BASE_BASEDATA bb1 ON tc.CLASS_TYPE = bb1.DATA_CODE LEFT JOIN BASE_BASEDATA bb2 ON tc.APPLY_WAY = bb2.DATA_CODE "
				+ "LEFT JOIN CLASS_SCOPE_DEPARTMENT csud ON tc.CLASS_ID = csud.class_id LEFT join questionnaire_survey_object qso ON qso.survey_object_id = tc.class_id "
				+ "LEFT JOIN ( SELECT tcu.class_id, count(tcu.CLASS_USER_ID) c1, count( CASE WHEN tcu.auditing_state IN (1, 5) THEN tcu.CLASS_USER_ID END ) c2 "
				+ "FROM TRAINING_CLASS_USER tcu WHERE tcu.auditing_state <> 6 GROUP BY tcu.class_id ) tcount ON tcount.class_id = tc.CLASS_ID WHERE 1 = 1  ");		
		if(DeptConstants.ZDBMJG.equals(queryCommond.getSearchClassTrainingType())){
			hql.append(" and tc.PUBLISH_STATE = 1 ");
		}
		
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		values.put("noState", ClassUser.IS_AUDITING_N_M);
		//培训机构id查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())){
			hql.append(" and tc.TRAINING_DEPT_ID like :trainingDeptId ");
			values.put("trainingDeptId", queryCommond.getSearchTrainingDeptId().trim());
		}
		//培训机构名称查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())){
			hql.append(" and tc.TRAINING_DEPT_NAME like :trainingDeptName ");
			values.put("trainingDeptName", "%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			hql.append(" and bb1.DATA_CODE = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}
		//班级学号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			hql.append(" and tc.CLASS_NUM like :classNum ");
			values.put("classNum", queryCommond.getSearchTrainingNum().trim());
		}

		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			hql.append(" and tc.CLASS_NAME like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		
		//主办单位查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorName())){
			hql.append(" and tc.sponsor_Name like :sponsorName");
			values.put("sponsorName","%"+queryCommond.getSearchSponsorName()+"%");
		}
		//项目上报状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchReportState())) {
			hql.append(" and tc.REPORT_STATE = :reportState");
			values.put("reportState", queryCommond.getSearchReportState());
		}

		//项目审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState()))
		{
			hql.append(" and tc.AUDIT_STATE = :auditState");
			values.put("auditState", queryCommond.getSearchAuditState());
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			hql.append(" and tc.begin_Date >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			hql.append(" and tc.signUp_State = :signUpState  ");
			values.put("signUpState",queryCommond.getSearchSignUpState());
		}
		//考核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchLockState())) {
			if(queryCommond.getSearchLockState().equals(TrainingClass.IS_LOCK_N)){
				hql.append(" and (tc.lock_state = :lockState or tc.lock_state is null) ");
			}else
				hql.append(" and tc.lock_state = :lockState  ");
			values.put("lockState",queryCommond.getSearchLockState());
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndTime())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			hql.append(" and tc.end_Date =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndTime().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDateStr())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			hql.append(" and tc.end_Date =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDateStr().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			hql.append(" and tc.end_Date <=:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNowDate())){
			hql.append(" and tc.end_Date =:endDate ");
			values.put("endDate", new Date());
		}

		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			hql.append(" and tc.open_State =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			hql.append(" and tc.end_State =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}

		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			hql.append(" and tc.publish_State =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			hql.append(" and tc.active_State =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			hql.append(" and tc.class_Id = :classId");
			values.put("classId", queryCommond.getSearchClassId().trim());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIds())){
			hql.append(" and tc.class_Id in (:classIds)");
			values.put("classIds", queryCommond.getSearchClassIds());
		}

		//干教管理员查询全部承办或主办，其他管理员只查询承办
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorId())){
			if(RoleConstants.ROLE_CODE_JW_ADMIN.equals(queryCommond.getSearchRoleCode())||RoleConstants.ROLE_CODE_GJC_ADMIN.equals(queryCommond.getSearchRoleCode())){
				//TODO 教务管理员看到所有班级	
			}/*else if(PropertyUtil.objectNotEmpty(queryCommond.getSearchFrameType())){
				//主办单位id
				hql.append(" and tc.SPONSOR_ID = :sponsorId ");
			}*/else{
				//主办单位id||承办单位ID
				hql.append(" and (tc.SPONSOR_ID = :sponsorId or csud.DEPT_ID = :sponsorId)  ");
			}
			values.put("sponsorId", queryCommond.getSearchSponsorId());
		}
		
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			if(sortInfo.getProperty().equals("tc.assess_start_time"))
				hql.append(" order by \"assessStartTime\" "+PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			else if(sortInfo.getProperty().equals("tc.assess_end_time"))
				hql.append(" order by \"assessEndTime\" "+PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			else if(sortInfo.getProperty().equals("reportedNumber"))
				hql.append(" order by \"reportedNumber\" "+PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			else
				hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			hql.append(" order by tc.create_Date desc");
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingClassQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		queryCondition.setQueryStringType(QueryStringType.SQL);
//		Long count = this.count(queryCondition);
		int count=this.executeFind(QueryStringType.SQL, hql.toString(), values).size();
		
		//拼写排序的sql end
		List<TrainingClassQueryBean> resultList=new ArrayList<TrainingClassQueryBean>();
		if(queryCommond.getLimit()==-1){
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition);
		}else {
			int firstResult = queryCommond.calculate(count);
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	
	/**
	 * 查询班级列表(报名用)
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2014-12-10
	 */
	@Override
	public List<TrainingClassQueryBean> findTrainingClassListSignUp(
			TrainingClassQueryCommond queryCommond) throws Exception {
		StringBuffer queryHql = new StringBuffer();
		//拼写查询的sql
		StringBuffer hql = new StringBuffer();
		hql.append("select distinct t.classId as classId, t.createDate as createDate,nvl(t.assessStartTime,t.endDate-1) as assessStartTime,nvl(t.assessEndTime,t.endDate+2) as assessEndTime, t.trainingDeptName as trainingDeptName,t.reportState as reportState,t.auditState as auditState," +
				"t.openState as openState, t.className as className, t.classNum as classNum,t.enterBeginTime as enterBeginTime,t.enterEndTime as enterEndTime," +
				"t.beginDate as beginDate,t.endDate as endDate,t.trainingHour as trainingHour,t.signUpState as signUpState,t.selectedLetters as selectedLetters,");
//		hql.append(" (select nvl(sum(cp.planCount),0) from ClassPlanApply cp where cp.classId=t.classId) as planCount,");//计划人数
		hql.append(" (select nvl(sum(cp.asignCount),0) from ClassPlanApply cp where cp.classId=t.classId) as kbCount,");//可报名额 add by NingSS at 20160217
		hql.append(" (select count(cu.classUserId) from ClassUser cu,DepartmentUser du,Department d " +
				"where cu.user.userId=du.user.userId and du.department.deptID=d.deptID and d.treepath like '"+queryCommond.getTreePath()+"%' and cu.trainingClass.classId=t.classId and cu.auditingState in (1,5)) as reportedNum,");//已报人数,审核通过人数
		
//		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5)) as reportedNum,");//已报人数,审核通过人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu,DepartmentUser du,Department d " +
				"where cu.user.userId=du.user.userId and du.department.deptID=d.deptID and d.treepath like '"+queryCommond.getTreePath()+"%' and cu.trainingClass.classId=t.classId) as reportedNumber,");//已报人数,包括审核通过、审核未通过、未审核
		hql.append(" t.publishState as publishState,t.activeState as activeState,ct.dataCode as classTypeCode ,ct.dataName as classTypeStr,t.perfectState as perfectState,t.allotDeptState as allotDeptState,");
		hql.append("t.sponsorID as sponsorID,t.sponsorName as sponsorName ");//主办单位部委id和名称
		hql.append(" FROM TrainingClass t left join t.classType ct left join t.applyWay aw left join t.signUpDepartment sd,Department d  where 1=1 and  d.deptID=t.sponsorID ");
		//查询数量的sql	
		StringBuffer countHql = new StringBuffer("FROM TrainingClass t left join t.classType ct left join t.applyWay aw left join t.signUpDepartment sd,Department d where 1=1  and  d.deptID=t.sponsorID ");
		
		if(DeptConstants.ZDBMJG.equals(queryCommond.getSearchClassTrainingType())){
			hql.append(" and t.publishState = 1 ");
			countHql.append(" and t.publishState = 1 ");
		}
		if(RoleConstants.ROLE_CODE_GJC_ADMIN.equals(queryCommond.getSearchRoleCode())){
			hql.append(" and t.sponsorID <> 1 ");
			countHql.append(" and t.sponsorID <> 1 ");
		}
		Object[] queryObj = buildQueryObject2(queryCommond, queryHql);
		queryHql = (StringBuffer)queryObj[0];
		Map<String,Object> values = (Map<String,Object>)queryObj[1];
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		countHql.append(queryHql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		//
		
		// 数据列表查询
		hql.append(queryHql);
		
		//拼写排序的sql begin
		
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			hql.append(" order by t.createDate desc");
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		List<TrainingClassQueryBean> resultList=new ArrayList<TrainingClassQueryBean>();
		if(queryCommond.getLimit()==-1){
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition1);
		}else {
			int firstResult = queryCommond.calculate(count.longValue());
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition1,firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	
	/**
	 * 查询班级列表(报名用)
	 * @param commond
	 * @return List<TrainingClassQueryBean>
	 * @throws Exception
	 * @author Rongsl
	 * @date 2017-02-06
	 */
	@Override
	public List<TrainingClassQueryBean> findTrainingClassListSignUp2( TrainingClassQueryCommond queryCommond) throws Exception {
		StringBuffer hql = new StringBuffer();
		hql.append(" select distinct tc.CLASS_ID as \"classId\", tc.CREATE_DATE as \"createDate\",tc.class_user_num \"classUserNum\", " +
				"STR_TO_DATE(IFNULL ( tc.ASSESS_START_TIME, tc.END_DATE - 1 ),'%Y-%m-%d') AS \"assessStartTime\", "+
				"STR_TO_DATE(IFNULL ( tc.ASSESS_END_TIME, tc.END_DATE + 2 ),'%Y-%m-%d') AS \"assessEndTime\", "+
				" tc.TRAINING_DEPT_NAME as \"trainingDeptName\",  ifnull(tcount.c1, 0) \"reportedNumber\", ifnull(tcount.c2, 0) \"reportedNum\", " +
				"tc.REPORT_STATE as \"reportState\", tc.AUDIT_STATE as \"auditState\", tc.OPEN_STATE as \"openState\", tc.CLASS_NAME as \"className\", " +
				"tc.CLASS_NUM as \"classNum\",  tc.ENTER_BEGIN_TIME as \"enterBeginTime\", tc.ENTER_END_TIME as \"enterEndTime\", tc.BEGIN_DATE as \"beginDate\", " +
				"tc.END_DATE as \"endDate\", tc.TRAINING_HOUR as \"trainingHour\", tc.SIGNUP_STATE as \"signUpState\",  tc.selected_letters as \"selectedLetters\", " +
				"tc.PUBLISH_STATE as \"publishState\", tc.ACTIVE_STATE as \"activeState\", bb1.DATA_CODE as \"classTypeCode\", bb1.DATA_NAME as \"classTypeStr\", " +
				"tc.PERFECT_STATE as \"perfectState\", tc.ALLOTDEPT_STATE as \"allotDeptState\",  tc.SPONSOR_ID as \"sponsorID\", tc.SPONSOR_NAME as \"sponsorName\" " +
				"from TRAINING_CLASS tc left outer join BASE_BASEDATA bb1 on tc.CLASS_TYPE = bb1.DATA_CODE left outer join BASE_BASEDATA bb2 on tc.APPLY_WAY = bb2.DATA_CODE " +
				"left outer join CLASS_SIGN_UP_DEPARTMENT csud on tc.CLASS_ID = csud.class_id left join (select tcu.class_id, count(distinct tcu.CLASS_USER_ID) c1, " +
				"count(distinct case when tcu.auditing_state in (1, 5) then tcu.CLASS_USER_ID  end) c2 from TRAINING_CLASS_USER  tcu, "+SystemConstants.SYSTEM_CODE+"uum_DEPARTMENT_USER bdu, "+SystemConstants.SYSTEM_CODE+"uum_DEPARTMENT bd2 " +
				" where tcu.USER_ID = bdu.USERID and tcu.auditing_state <> :noState and bdu.DEPID = bd2.DEPID and INSTR (bd2.TREEPATH, '"+queryCommond.getTreePath()+"')=1 "+
				"group by tcu.class_id) tcount  on tcount.class_id = tc.CLASS_ID, BASE_DEPARTMENT bd " +
				" where 1 = 1 and bd.DEPID = tc.SPONSOR_ID  ");		
		if(DeptConstants.ZDBMJG.equals(queryCommond.getSearchClassTrainingType())){
			hql.append(" and tc.PUBLISH_STATE = 1 ");
		}
		if(RoleConstants.ROLE_CODE_GJC_ADMIN.equals(queryCommond.getSearchRoleCode())){
			hql.append(" and tc.SPONSOR_ID <> 1 ");
		}
		
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		values.put("noState", ClassUser.IS_AUDITING_N_M);
		
		//培训机构id查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())){
			hql.append(" and tc.TRAINING_DEPT_ID like :trainingDeptId ");
			values.put("trainingDeptId", queryCommond.getSearchTrainingDeptId().trim());
		}
		//培训机构名称查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())){
			hql.append(" and tc.TRAINING_DEPT_NAME like :trainingDeptName ");
			values.put("trainingDeptName", "%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			hql.append(" and bb1.DATA_CODE = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}
		//主办单位查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorName())){
			hql.append(" and tc.sponsor_Name like :sponsorName");
			values.put("sponsorName","%"+queryCommond.getSearchSponsorName()+"%");
		}
		//班级学号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			hql.append(" and tc.CLASS_NUM like :classNum ");
			values.put("classNum", queryCommond.getSearchTrainingNum().trim());
		}

		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			hql.append(" and tc.CLASS_NAME like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//项目上报状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchReportState())) {
			hql.append(" and tc.REPORT_STATE = :reportState");
			values.put("reportState", queryCommond.getSearchReportState());
		}

		//项目审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState()))
		{
			hql.append(" and tc.AUDIT_STATE = :auditState");
			values.put("auditState", queryCommond.getSearchAuditState());
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			hql.append(" and tc.begin_Date >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			hql.append(" and tc.signUp_State = :signUpState  ");
			values.put("signUpState",queryCommond.getSearchSignUpState());
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndTime())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			hql.append(" and tc.end_Date =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndTime().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDateStr())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			hql.append(" and tc.end_Date =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDateStr().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			hql.append(" and tc.end_Date <=:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNowDate())){
			hql.append(" and tc.end_Date =:endDate ");
			values.put("endDate", new Date());
		}

		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			hql.append(" and tc.open_State =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			hql.append(" and tc.end_State =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}

		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			hql.append(" and tc.publish_State =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			hql.append(" and tc.active_State =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			hql.append(" and tc.class_Id = :classId");
			values.put("classId", queryCommond.getSearchClassId().trim());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIds())){
			hql.append(" and tc.class_Id in (:classIds)");
			values.put("classIds", queryCommond.getSearchClassIds());
		}

		//如果是一级机构则显示干教处建的培训班
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorId())&&PropertyUtil.objectNotEmpty(queryCommond.getUnitLevel())&&queryCommond.getUnitLevel().equals(TrainingClass.MECHANISM_TYPE_ONE)) {
			hql.append(" and (csud.DEPT_ID = :sponsorId or csud.DEPT_ID = 1)  ");
		}else{
			hql.append(" and csud.DEPT_ID = :sponsorId ");
		}
		
		values.put("sponsorId", queryCommond.getSearchSponsorId());
		
//		hql.append(" order by tc.CREATE_DATE desc  ");

		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			hql.append(" order by tc.create_Date desc");
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingClassQueryBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		queryCondition.setQueryStringType(QueryStringType.SQL);
//		Long count = this.count(queryCondition);
		int count=this.executeFind(QueryStringType.SQL, hql.toString(), values).size();
		
		//拼写排序的sql end
		List<TrainingClassQueryBean> resultList=new ArrayList<TrainingClassQueryBean>();
		if(queryCommond.getLimit()==-1){
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition);
		}else {
			int firstResult = queryCommond.calculate(count);
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	//[0] hql String
	//[1] params Map<String,Object>
	public Object[] buildQueryObject(TrainingClassQueryCommond queryCommond,StringBuffer queryHql) throws Exception{
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();

		//培训机构id查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())){
			queryHql.append(" and t.trainingDeptId like :trainingDeptId ");
			values.put("trainingDeptId", queryCommond.getSearchTrainingDeptId().trim());
		}
		//培训机构名称查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())){
			queryHql.append(" and t.trainingDeptName like :trainingDeptName ");
			values.put("trainingDeptName", "%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			queryHql.append(" and t.classType.dataCode = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}
		//班级学号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			queryHql.append(" and t.classNum like :classNum ");
			values.put("classNum", queryCommond.getSearchTrainingNum().trim());
		}

		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			queryHql.append(" and t.className like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//项目上报状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchReportState())) {
			queryHql.append(" and t.reportState = :reportState");
			values.put("reportState", queryCommond.getSearchReportState());
		}

		//项目审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState()))
		{
			queryHql.append(" and t.auditState = :auditState");
			values.put("auditState", queryCommond.getSearchAuditState());
		}
		//主办单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName())) {
			queryHql.append(" and t.organizerName like :organizerName ");
			values.put("organizerName", "%"+queryCommond.getSearchOrganizerName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			queryHql.append(" and t.beginDate = :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			queryHql.append(" and t.signUpState = :signUpState  ");
			values.put("signUpState",queryCommond.getSearchSignUpState());
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndTime())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.endDate =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndTime().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDateStr())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.endDate =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDateStr().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.endDate =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNowDate())){
			queryHql.append(" and t.endDate <=:endDate ");
			values.put("endDate", new Date());
		}

		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			queryHql.append(" and t.openState =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			queryHql.append(" and t.endState =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}

		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			queryHql.append(" and t.publishState =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			queryHql.append(" and t.activeState =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			queryHql.append(" and t.classId = :classId");
			values.put("classId", queryCommond.getSearchClassId().trim());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIds()))
		{
			queryHql.append(" and t.classId in (:classIds)");
			values.put("classIds", queryCommond.getSearchClassIds());
		}
		//部委选学报名系统字段
		//部委id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieID())) {
			queryHql.append(" and t.ministrieID = :ministrieId");
			values.put("ministrieId", queryCommond.getSearchMinistrieID());
		}
		//主办单位id||承办单位ID
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorId())) {
			queryHql.append(" and (t.sponsorID = :sponsorId or sd.dept.deptID=:sponsorId ) ");
			values.put("sponsorId", queryCommond.getSearchSponsorId());
		}
		//部委名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieName())) {
			queryHql.append(" and t.ministrieName like :ministrieName");
			values.put("ministrieName","%"+queryCommond.getSearchMinistrieName()+"%");
		}
		
		//主办单位查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorName())){
			queryHql.append(" and t.sponsorName like :sponsorName");
			values.put("sponsorName","%"+queryCommond.getSearchSponsorName()+"%");
		}
//		//加入机构等级判断（一级管理员）
//		if(PropertyUtil.objectNotEmpty(queryCommond.getUnitLevel())&&queryCommond.getUnitLevel().equals(TrainingClass.MECHANISM_TYPE_ONE)) {
//			queryHql.append(" and (d.adminLevelCode =:adminLevelCode or d.treepath like :treePath)");
//			values.put("adminLevelCode",queryCommond.getUnitLevel());
//			values.put("treePath",queryCommond.getTreePath()+"%");
//		}
//		
//		//加入机构等级判断（二级管理员）
//		if(PropertyUtil.objectNotEmpty(queryCommond.getUnitLevel())&&queryCommond.getUnitLevel().equals(TrainingClass.MECHANISM_TYPE_TWO)) {
////					queryHql.append(" and d.adminLevelCode = :adminLevelCode ");
////					values.put("adminLevelCode",queryCommond.getUnitLevel()+"%");
//
//			String ids[]=queryCommond.getTreePath().split("/");
//			StringBuffer id=new StringBuffer("");
//			queryHql.append(" and  (d.treepath =:ids0");
//			id.append(ids[0]+"/");
//			values.put("ids0", id);
//			for(int i=1;i<ids.length;i++){
//				queryHql.append(" or d.treepath =:ids"+i);
//				id.append(ids[i]+"/");
//				values.put("ids"+i, id);
//			}
//			queryHql.append(")");
//		}
//
//		if (PropertyUtil.objectNotEmpty(queryCommond.getDeptId()))
//			values.put("scope", "%/" + queryCommond.getDeptId() + "/%");
				
		//拼写查询条件sql end
		return new Object[]{queryHql,values};
	}

	public Object[] buildQueryObject2(TrainingClassQueryCommond queryCommond,StringBuffer queryHql) throws Exception{
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();

		//培训机构id查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())){
			queryHql.append(" and t.trainingDeptId like :trainingDeptId ");
			values.put("trainingDeptId", queryCommond.getSearchTrainingDeptId().trim());
		}
		//培训机构名称查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())){
			queryHql.append(" and t.trainingDeptName like :trainingDeptName ");
			values.put("trainingDeptName", "%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			queryHql.append(" and t.classType.dataCode = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}
		//班级学号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			queryHql.append(" and t.classNum like :classNum ");
			values.put("classNum", queryCommond.getSearchTrainingNum().trim());
		}

		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			queryHql.append(" and t.className like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//项目上报状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchReportState())) {
			queryHql.append(" and t.reportState = :reportState");
			values.put("reportState", queryCommond.getSearchReportState());
		}

		//项目审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState()))
		{
			queryHql.append(" and t.auditState = :auditState");
			values.put("auditState", queryCommond.getSearchAuditState());
		}
		//主办单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName())) {
			queryHql.append(" and t.organizerName like :organizerName ");
			values.put("organizerName", "%"+queryCommond.getSearchOrganizerName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			queryHql.append(" and t.beginDate = :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			queryHql.append(" and t.signUpState = :signUpState  ");
			values.put("signUpState",queryCommond.getSearchSignUpState());
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndTime())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.endDate =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndTime().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDateStr())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.endDate =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDateStr().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.endDate =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNowDate())){
			queryHql.append(" and t.endDate =:endDate ");
			values.put("endDate", new Date());
		}

		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			queryHql.append(" and t.openState =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			queryHql.append(" and t.endState =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}

		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			queryHql.append(" and t.publishState =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			queryHql.append(" and t.activeState =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			queryHql.append(" and t.classId = :classId");
			values.put("classId", queryCommond.getSearchClassId().trim());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIds()))
		{
			queryHql.append(" and t.classId in (:classIds)");
			values.put("classIds", queryCommond.getSearchClassIds());
		}
		//部委选学报名系统字段
		//部委id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieID())) {
			queryHql.append(" and t.ministrieID = :ministrieId");
			values.put("ministrieId", queryCommond.getSearchMinistrieID());
		}
		//部委名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieName())) {
			queryHql.append(" and t.ministrieName like :ministrieName");
			values.put("ministrieName","%"+queryCommond.getSearchMinistrieName()+"%");
		}

		//如果是一级机构则显示干教处建的培训班
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorId())&&PropertyUtil.objectNotEmpty(queryCommond.getUnitLevel())&&queryCommond.getUnitLevel().equals(TrainingClass.MECHANISM_TYPE_ONE)) {
			queryHql.append(" and (sd.dept.deptID=:sponsorId or sd.dept.deptID=1) ");
			values.put("sponsorId", queryCommond.getSearchSponsorId());
		}else{
			queryHql.append(" and sd.dept.deptID=:sponsorId ");
			values.put("sponsorId", queryCommond.getSearchSponsorId());
		}
		
//		//培训班报名只需要查询已发布，为开班，正在报名状态的培训班
//		queryHql.append(" and t.publishState=:publishState and t.openState=:openState and t.signUpState=:signUpState ");
//		values.put("publishState", TrainingClass.IS_PUBLISH_Y);
//		values.put("openState", TrainingClass.IS_OPENSTATE_N);
//		values.put("signUpState", TrainingClass.IS_SIGNUP_BEGIN);
		values.put("sponsorId", queryCommond.getSearchSponsorId());
		
		//加入机构等级判断（一级管理员）
//		if(PropertyUtil.objectNotEmpty(queryCommond.getUnitLevel())&&queryCommond.getUnitLevel().equals(TrainingClass.MECHANISM_TYPE_ONE)) {
//			queryHql.append(" and (d.adminLevelCode =:adminLevelCode or d.treepath like :treePath)");
//			values.put("adminLevelCode",queryCommond.getUnitLevel());
//			values.put("treePath",queryCommond.getTreePath()+"%");
//		}
		
		//加入机构等级判断（二级管理员）
//		if(PropertyUtil.objectNotEmpty(queryCommond.getUnitLevel())&&queryCommond.getUnitLevel().equals(TrainingClass.MECHANISM_TYPE_TWO)) {
////			queryHql.append(" and d.adminLevelCode = :adminLevelCode ");
////			values.put("adminLevelCode",queryCommond.getUnitLevel()+"%");
//
//			String ids[]=queryCommond.getTreePath().split("/");
//			StringBuffer id=new StringBuffer("");
//			queryHql.append(" and  (d.treepath =:ids0");
//			id.append(ids[0]+"/");
//			values.put("ids0", id);
//			for(int i=1;i<ids.length;i++){
//				queryHql.append(" or d.treepath =:ids"+i);
//				id.append(ids[i]+"/");
//				values.put("ids"+i, id);
//			}
//			queryHql.append(")");
//		}
		//拼写查询条件sql end
		return new Object[]{queryHql,values};
	}

	/**
	 * 查询班级列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2015-03-26
	 */
	@Override
	public List<TrainingClassQueryBean> findTrainingClassListFinish(
			TrainingClassQueryCommond queryCommond) throws Exception {
		StringBuffer queryHql = new StringBuffer();
		//拼写查询的sql
		StringBuffer hql = new StringBuffer();

		hql.append("select t.classId as classId, t.trainingDeptName as trainingDeptName ,t.reportState as reportState,t.auditState as auditState,t.classUserNum as classUserNum," +
				"t.openState as openState, t.className as className, t.classNum as classNum," +
				"t.beginDate as beginDateNoYear,t.endDate as endDateNoYear,t.enterBeginTime as enterBeginTime,t.enterEndTime as enterEndTime,t.trainingHour as trainingHour,t.signUpState as signUpState,");
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5)) as reportedNum,");//已报人数,审核通过人数
		/*hql.append(" ((select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.attendanceState=1)/(select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState=1)as attendanceRate),");*///全勤人数,审核通过人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5) and cu.attendanceState=1) as allAttendance,");//全勤人数,审核通过人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5) and cu.attendanceState=2) as absenceAttendance,");//缺勤人数,审核通过人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5) and cu.attendanceState=3) as leaveAttendance,");//请假人数,审核通过人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5) and cu.attendanceState=4) as noAttendance,");//未出勤,审核通过人数
		//hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId) as reportedNumber,");//已报人数,包括审核通过、审核未通过、未审核
		hql.append(" t.publishState as publishState,t.activeState as activeState,ct.dataCode as classTypeCode ,ct.dataName as classTypeStr,t.perfectState as perfectState,t.allotDeptState as allotDeptState");
		hql.append(" FROM TrainingClass t left join t.classType ct left join t.applyWay aw where 1=1");
		//查询数量的sql	
		StringBuffer countHql = new StringBuffer("FROM TrainingClass t left join t.classType ct where 1=1 ");

		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();

		//培训机构id查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())){
			queryHql.append(" and t.trainingDeptId like :trainingDeptId ");
			values.put("trainingDeptId", queryCommond.getSearchTrainingDeptId().trim());
		}
		//培训机构名称查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())){
			queryHql.append(" and t.trainingDeptName like :trainingDeptName ");
			values.put("trainingDeptName", "%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			queryHql.append(" and t.classType.dataCode = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}

		//班级学号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			queryHql.append(" and t.classNum like :classNum ");
			values.put("classNum", "%"+queryCommond.getSearchTrainingNum().trim()+"%");
		}

		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			queryHql.append(" and t.classMainName like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//项目上报状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchReportState())) {
			queryHql.append(" and t.reportState = :reportState");
			values.put("reportState", queryCommond.getSearchReportState());
		}

		//项目审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState()))
		{
			queryHql.append(" and t.auditState = :auditState");
			values.put("auditState", queryCommond.getSearchAuditState());
		}
		//主办单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName())) {
			queryHql.append(" and t.organizerName like :organizerName ");
			values.put("organizerName", "%"+queryCommond.getSearchOrganizerName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			queryHql.append(" and t.beginDate = :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			queryHql.append(" and t.signUpState = :signUpState  ");
			values.put("signUpState",queryCommond.getSearchSignUpState());
		}

		//开班结束时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndTime())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.endDate =:enterEndTime");
			values.put("enterEndTime", DateUtil.getDateByString(queryCommond.getSearchEndTime().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDateStr())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.endDate =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDateStr().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNowDate())){
			queryHql.append(" and t.endDate <=:endDate ");
			values.put("endDate", new Date());
		}

		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			queryHql.append(" and t.openState =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			queryHql.append(" and t.endState =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}

		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			queryHql.append(" and t.publishState =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			queryHql.append(" and t.activeState =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId()))
		{
			queryHql.append(" and t.classId = :classId");
			values.put("classId", queryCommond.getSearchClassId().trim());
		}
		//拼写查询条件sql end

		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
		.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		countHql.append(queryHql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		//
		int firstResult = queryCommond.calculate(count.longValue());

		// 数据列表查询
		hql.append(queryHql);

		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			hql.append(" order by t.endDate desc, t.createDate desc");
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);

		return (List<TrainingClassQueryBean>) this.findList(queryCondition1,
				firstResult, queryCommond.getLimit());
	}
	/**
	 * 班级培训班更新（局部属性更新, 更新条件暂只支持ids）
	 * @param trainingClassPartFieldUpdateBean
	 * @throws Exception
	 * @author ZhaoQT
	 * @date 2013-12-17
	 */
	@Override
	public void updateTrainingClassPartField(
			TrainingClassPartFieldUpdateBean trainingClassPartFieldUpdateBean,String classId)
					throws Exception {
		TrainingClassQueryCommond trainingClassQueryCommond = (TrainingClassQueryCommond)
				trainingClassPartFieldUpdateBean.getiQueryCommond();
		StringBuffer buffer = new StringBuffer("update TrainingClass tc set tc.classId = tc.classId");
		Map<String, Object> fieldValue = trainingClassPartFieldUpdateBean.getFieldValue();
		if(fieldValue != null) {
			// 拼接update set 字段
			Set<Map.Entry<String, Object>> set = fieldValue.entrySet();
			for (Iterator<Map.Entry<String, Object>> it = set.iterator(); it.hasNext();) {
				Map.Entry<String, Object> entry = (Map.Entry<String, Object>) it.next();
				String fieldName = entry.getKey();
				if(!"classId".equals(fieldName)){
					buffer.append(", tc." + fieldName + "=:" + fieldName);
				}
			}
		} else {
			fieldValue = new HashMap<String, Object>();
		}
		buffer.append(" where 1=1 ");
		// 拼接 where
		if(PropertyUtil.objectNotEmpty(classId)) {
			buffer.append(" and tc.classId = :classId ");
			fieldValue.put("classId", classId);
		}else{
			if(trainingClassQueryCommond != null) {
				if(PropertyUtil.objectNotEmpty(trainingClassQueryCommond.getSearchUpdateClassIds())) {
					buffer.append(" and tc.classId in (:searchUpdateClassIds)");
					fieldValue.put("searchUpdateClassIds", trainingClassQueryCommond.getSearchUpdateClassIds());
				}
			}
		}
		this.executeUpdate(QueryStringType.HQL, buffer.toString(), fieldValue);
	}


	/**
	 * 在添加和修改的时候判断培训班名称是否一样
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2013-12-17
	 */
	@Override
	public boolean decideClassName(TrainingClassValidCommond vCommond) throws Exception {

		StringBuffer hql = new StringBuffer();
		Map<String, Object> values = new HashMap<String, Object>();

		hql.append("SELECT t FROM TrainingClass t where 1=1");
//		if(PropertyUtil.objectNotEmpty(vCommond.getclassnameYear())) {
//			hql.append(" and t.classnameYear=:classnameYear");
//			values.put("classnameYear", vCommond.getclassnameYear());
//		}

		//去掉期数 by Max at 2015-01-21
		//		if(PropertyUtil.objectNotEmpty(vCommond.getClassnamePeriod())) {
		//			hql.append(" and t.classnamePeriod.dataCode=:classnamePeriod");
		//			values.put("classnamePeriod", vCommond.getClassnamePeriod());
		//		}

//		if(PropertyUtil.objectNotEmpty(vCommond.getClassName())) {
//			hql.append(" and t.className=:className");
//			values.put("className", vCommond.getClassName());
//		}
		if(PropertyUtil.objectNotEmpty(vCommond.getClassNum())){
			hql.append(" and t.classNum=:classNum ");
			values.put("classNum", vCommond.getClassNum());
		}
		if(PropertyUtil.objectNotEmpty(vCommond.getEntityID())) {
			hql.append(" and t.classId !=:classId");
			values.put("classId",vCommond.getEntityID());
		}
		
		if(PropertyUtil.objectNotEmpty(vCommond.getMinistrieID())) {
			hql.append(" and t.ministrieID=:ministrieId");
			values.put("ministrieId", vCommond.getMinistrieID());
		}

		hql.append(" and t.activeState=:activeState");
		values.put("activeState", TrainingClass.IS_ACTIVE_Y);

		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		List<?> list=this.executeFind(QueryStringType.HQL, hql.toString(), values, -1, -1);
		if(list!=null&&list.size()>0) {
			//表示有重名  不能进行添加
			return true;
		}
		return false;
	}	

	/**
	 * 单位级项目查询班级列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2013-12-23
	 */
	@Override
	public List<TrainingClassQueryBean> findTrainingClassListForUnitProject(
			TrainingClassQueryCommond queryCommond) throws Exception {
		//拼写查询的sql
		StringBuffer listhql = new StringBuffer();
		StringBuffer counthql = new StringBuffer();

		listhql=findTrainingClassListForUnitProjectSql(queryCommond).append(querySql(queryCommond).append(") t where 1=1"));
		counthql=findTrainingClassListForUnitProjectCountSql(queryCommond).append(querySql(queryCommond).append(") t where 1=1"));

		//学员状态判断
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchApprovalStatus())) {
			//			classStuNum;//总人数
			//			reportedNum;//审核通过的人数
			//			auditNoPass;//审核未通过的人数
			//			noAudit;//未审核的人数
			//			1：审核通过
			//			2：未审核
			//			3：审核未通过
			//			4：审核中
			if(queryCommond.getSearchApprovalStatus().equals(1)) {
				listhql.append(" and t.classStuNum>0 and t.classStuNum-t.reportedNum=0");
				counthql.append(" and t.classStuNum>0 and t.classStuNum-t.reportedNum=0");
			}else if(queryCommond.getSearchApprovalStatus().equals(2)) {
				listhql.append(" and (t.classStuNum>0 and t.noAudit>0 and t.classStuNum-t.noAudit=0 )or(t.classStuNum=0)");
				counthql.append(" and ((t.classStuNum>0 and t.noAudit>0 and t.classStuNum-t.noAudit=0 )or(t.classStuNum=0))");
			}else if(queryCommond.getSearchApprovalStatus().equals(3)) {
				listhql.append(" and t.classStuNum>0 and t.auditNoPass>0 and t.classStuNum-t.reportedNum-t.auditNoPass=0");
				counthql.append(" and t.classStuNum>0 and t.auditNoPass>0 and t.classStuNum-t.reportedNum-t.auditNoPass=0");
			}else if(queryCommond.getSearchApprovalStatus().equals(4)) {
				listhql.append(" and t.classStuNum>0 and t.noAudit>0 and t.classStuNum-t.noAudit<>0");
				counthql.append(" and t.classStuNum>0 and t.noAudit>0 and t.classStuNum-t.noAudit<>0");
			}
		}


		//查询总数量
		List<BigInteger> executeFind=(List<BigInteger>) this.executeFind(QueryStringType.SQL, counthql.toString(), null);
		Long count=Long.valueOf(executeFind.get(0).toString());
		//查询总数量
		//计算分页
		int firstResult = queryCommond.calculate(count.longValue());
		int maxResult=queryCommond.getLimit();
		//计算分页
		//查询逻辑
		TrainingClassQueryCondition condition=new TrainingClassQueryCondition();
		condition.setQueryString(listhql.toString());
		BeanResultMapper mapper=(BeanResultMapper)ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(TrainingClassQueryBean.class);
		condition.setResultMapper(mapper);
		condition.setQueryStringType(QueryStringType.SQL);
		List<TrainingClassQueryBean> List=(List<TrainingClassQueryBean>)this.findList(condition, firstResult, maxResult);
		//查询逻辑

		return List;
	}

	private StringBuffer findTrainingClassListForUnitProjectSql(TrainingClassQueryCommond queryCommond) {
		StringBuffer hql = new StringBuffer();
		hql.append(" select t.ORGANIZER_ID as organizerIdStr,t.AUDITING_DATE as auditingDate,t.CLASS_ID as classId, t.CLASS_MAIN_NAME as className, t.CLASSNAME_YEAR as classnameYear,t.CLASSNAME_PERIOD_TYPE as classnamePeriodType,t.CLASSNAME_PERIOD as classnamePeriod,t.classType as classType,t.BEGIN_DATE as beginDate,t.END_DATE as endDate,");
		hql.append(" t.CLASSNAME_UNIT as classnameUnit,t.TRAINING_DAY as trainingDayBig ,t.TRAINING_HOUR as trainingHourBig,t.applyWay as applyWay,");
		hql.append(" CONVERT(t.reportedNum,SIGNED) as reportedNumBig,");//已报人数
		hql.append(" CONVERT(t.CLASS_USER_NUM ,SIGNED)as classUserNumBig, t.ORGANIZER_NAME as organizerName,");
		hql.append(" CONVERT(t.OPEN_STATE,SIGNED) as openStateBig,CONVERT(t.END_STATE,SIGNED) as endStateBig,");
		hql.append(" CONVERT(t.PUBLISH_STATE,SIGNED) as publishStateBig,CONVERT(t.ACTIVE_STATE,SIGNED) as activeStateBig, t.applyOrganCode as applyOrganCode,");
		hql.append(" CONVERT(t.REPORT_STATE,SIGNED) as reportStateBig,CONVERT(t.AUDIT_RESULT,SIGNED) as auditStateBig,");
		hql.append(" CONVERT(t.classStuNum,SIGNED) as classStuNumBig,CONVERT(t.auditNoPass,SIGNED) as auditNoPassBig,CONVERT(t.noAudit,SIGNED) as noAuditBig ");
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchType())&&queryCommond.getSearchType().equals("dwbsh")) {
			hql.append(" ,CONVERT(t.noAuditHours,SIGNED) as noAuditHoursBig");
		}
		hql.append(" from (");

		hql.append(" select train.*,basedata1_.DATA_NAME as classType,basedata2_.DATA_NAME as applyWay,");
		//已报名的人数
		hql.append(" (select count(classuser4_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser4_ where classuser4_.CLASS_ID=train.CLASS_ID and classuser4_.AUDITING_STATE=1) as reportedNum,");
		hql.append(" basedata3_.DATA_CODE as applyOrganCode,");
		//班级所有人员
		hql.append(" (select count(classuser5_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser5_ where classuser5_.CLASS_ID=train.CLASS_ID) as classStuNum,");
		//审核未通过的人员
		hql.append(" (select count(classuser6_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser6_ where classuser6_.CLASS_ID=train.CLASS_ID and classuser6_.AUDITING_STATE=2) as auditNoPass,");
		//未审核的人员
		hql.append(" (select count(classuser7_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser7_ where classuser7_.CLASS_ID=train.CLASS_ID and classuser7_.AUDITING_STATE=3) as noAudit ");
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchType())&&queryCommond.getSearchType().equals("dwbsh")) {
			//单位班审核时候加上学时未审核人数查询
			hql.append(", (select count(classuser8_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser8_ where classuser8_.CLASS_ID=train.CLASS_ID and classuser8_.AUDITING_STATE=1 and classuser8_.AUDITING_HOURS_STATE=3) as noAuditHours");
		}

		hql.append(" from TRAINING_CLASS as train");
		hql.append(" left outer join BASE_BASEDATA basedata1_ on train.CLASS_TYPE=basedata1_.DATA_CODE left outer join BASE_BASEDATA basedata2_ on train.APPLY_WAY=basedata2_.DATA_CODE left outer join BASE_BASEDATA basedata3_ on train.SET_APPLY_ORGAN=basedata3_.DATA_CODE");
		hql.append(" where 1=1 ");
		return hql;
	}

	private StringBuffer findTrainingClassListForUnitProjectCountSql(TrainingClassQueryCommond queryCommond) {
		StringBuffer hql = new StringBuffer();
		hql.append(" select count(*) from (");
		hql.append(" select train.*,basedata1_.DATA_NAME as classType,basedata2_.DATA_NAME as applyWay,");
		hql.append(" (select count(classuser4_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser4_ where classuser4_.CLASS_ID=train.CLASS_ID and classuser4_.AUDITING_STATE=1) as reportedNum,");
		hql.append(" basedata3_.DATA_CODE as applyOrganCode,");
		hql.append(" (select count(classuser5_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser5_ where classuser5_.CLASS_ID=train.CLASS_ID) as classStuNum,");
		hql.append(" (select count(classuser6_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser6_ where classuser6_.CLASS_ID=train.CLASS_ID and classuser6_.AUDITING_STATE=2) as auditNoPass,");
		hql.append(" (select count(classuser7_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser7_ where classuser7_.CLASS_ID=train.CLASS_ID and classuser7_.AUDITING_STATE=3) as noAudit ");
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchType())&&queryCommond.getSearchType().equals("dwbsh")) {
			//单位班审核时候加上学时未审核人数查询
			hql.append(", (select count(classuser8_.CLASS_USER_ID) from TRAINING_CLASS_USER classuser8_ where classuser8_.CLASS_ID=train.CLASS_ID and classuser8_.AUDITING_STATE=1 and classuser8_.AUDITING_HOURS_STATE=3) as noAuditHours");
		}
		hql.append(" from TRAINING_CLASS as train");
		hql.append(" left outer join BASE_BASEDATA basedata1_ on train.CLASS_TYPE=basedata1_.DATA_CODE left outer join BASE_BASEDATA basedata2_ on train.APPLY_WAY=basedata2_.DATA_CODE left outer join BASE_BASEDATA basedata3_ on train.SET_APPLY_ORGAN=basedata3_.DATA_CODE");
		hql.append(" where 1=1 ");
		return hql;
	}

	private StringBuffer querySql(TrainingClassQueryCommond queryCommond) {
		StringBuffer queryHql = new StringBuffer();
		//培训班类别
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassCategory())){
			queryHql.append(" and train.CLASS_CATEGORY ='"+queryCommond.getSearchClassCategory().trim()+"'");
		}
		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			queryHql.append(" and train.CLASS_MAIN_NAME like '%"+queryCommond.getSearchClassName().trim()+"%'");
		}
		//培训班类型查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())) {
			queryHql.append(" and basedata1_.DATA_CODE ='"+queryCommond.getSearchClassType().trim()+"'");
		}
		//主办单位 按照名称查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName())) {
			queryHql.append(" and train.ORGANIZER_NAME like '%"+queryCommond.getSearchOrganizerName().trim()+"%'");
		}

		//主办单位 按照id查询
		//一级管理员的单位上报区级项目审核更改为根据treePath查询，包含子单位  By HuangTF At 2014-06-17
		//二级管理员的查询单位上报区级项目只查询当前单位创建的班级
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerId())) {
			if(PropertyUtil.objectNotEmpty(queryCommond.getTreePath()))
			{
				queryHql.append(" and train.ORGANIZER_ID in (select bd.DEPID from base_department bd where bd.TREEPATH like '"+queryCommond.getTreePath()+"%')");
			}
			else
			{
				//				if(PropertyUtil.objectNotEmpty(queryCommond.getSearchType())&&queryCommond.getSearchType().equals("dwbsh")) {
				//					//如果单位是房山区，则查询所有的部门。
				//					if(!queryCommond.getSearchOrganizerId().equals("1854247")) {
				//						queryHql.append(" and train.ORGANIZER_ID = '"+queryCommond.getSearchOrganizerId().trim()+"'");
				//					}
				//				}else {
				//						queryHql.append(" and train.ORGANIZER_ID = '"+queryCommond.getSearchOrganizerId().trim()+"'");
				//				}
				queryHql.append(" and train.ORGANIZER_ID = '"+queryCommond.getSearchOrganizerId().trim()+"'");
			}

		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			queryHql.append(" and train.BEGIN_DATE like '"+queryCommond.getSearchBeginDate()+" %'");
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			queryHql.append(" and train.END_DATE like '"+queryCommond.getSearchEndDate()+" %'");
		}

		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			queryHql.append(" and train.OPEN_STATE ='"+queryCommond.getSearchOpenState()+"'");
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			queryHql.append(" and train.END_STATE ='"+queryCommond.getSearchEndState()+"'");
		}
		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			queryHql.append(" and train.PUBLISH_STATE ='"+queryCommond.getSearchPublishState()+"'");
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			queryHql.append(" and train.ACTIVE_STATE ='"+queryCommond.getSearchActiveState()+"'");
		}
		//专题班id查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId()))
		{
			queryHql.append(" and train.CLASS_ID ='"+queryCommond.getSearchClassId()+"'");
		}
		//上报状态查询
		//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchReportStatus())) {
		//			queryHql.append(" and train.REPORT_STATE='"+queryCommond.getSearchReportStatus()+"'");
		//		}
		//项目审核状态查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState())) {
			queryHql.append(" and train.AUDIT_RESULT='"+queryCommond.getSearchAuditState()+"'");
		}

		//根据专题班id集合查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUpdateClassIds())) {
			String [] cids=queryCommond.getSearchUpdateClassIds();
			String classIds="";
			for(int i=0;i<cids.length;i++) {
				classIds=classIds+"'"+cids[i]+"',";
			}

			classIds=classIds.substring(0, classIds.length()-1);
			queryHql.append(" and train.CLASS_ID in("+classIds+")");
		}
		//排序
		queryHql.append(" order by train.CREATE_DATE desc");
		return queryHql;
		//拼写查询条件sql end
	}


	/**
	 * 查询班级列表(区级项目报名列表)
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @date 2013-12-25
	 */
	@Override
	public List<TrainingClassQueryBean> findOrganizationTrainingClassList(
			TrainingClassQueryCommond queryCommond) throws Exception {
		StringBuffer queryHql = new StringBuffer();
		//拼写查询的sql
		StringBuffer hql = new StringBuffer();
		hql.append("select cad.department.deptID as deptID,t.classId as classId, t.classMainName as className, t.classnameYear as classnameYear,t.classnamePeriodType as classnamePeriodType,t.classnamePeriod as classnamePeriod,ct.dataName as classType,t.enterBeginTime as enterBeginTime,t.enterEndTime as enterEndTime,");
		hql.append(" t.classnameUnit as classnameUnit,t.trainingDay as trainingDay ,t.trainingHour as trainingHour,aw.dataName as applyWay,");
		hql.append(" (select count(cu.classUserId) from ClassUser cu,DepartmentUser dudu where cu.trainingClass.classId=t.classId and cu.user.userId = dudu.user.userId and dudu.department.treepath like :treePath and cu.auditingState in (1,5)) as reportedNum,");//审核通过人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu,DepartmentUser dudu where cu.trainingClass.classId=t.classId and cu.user.userId = dudu.user.userId and dudu.department.treepath like :treePath) as reportedNumber,");//已报人数
		hql.append(" t.classUserNum as classUserNum, t.organizerName as organizerName,cad.department.deptName as deptName,cad.restrictPersonNum as restrictPersonNum,");
		hql.append(" t.openState as openState,t.endState as endState,");
		hql.append(" t.publishState as publishState,t.activeState as activeState, sn.dataCode as applyOrganCode");
		hql.append(" FROM ClassApplyDep cad right join cad.trainingClass t left join t.classType ct left join t.applyWay aw left join t.setApplyOrgan sn left join cad.department dept  where t.publishState=1 ");
		//查询数量的sql	
		StringBuffer countHql = new StringBuffer("FROM ClassApplyDep cad right join cad.trainingClass t left join t.classType ct left join t.applyWay aw left join t.setApplyOrgan sn left join cad.department dept  where t.publishState=1 ");
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		//查询已报人数时包括其下级单位
		values.put("treePath", queryCommond.getTreePath()+"%");
		//单位id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())){
			queryHql.append(" and (dept.deptID =:deptID ");
			values.put("deptID", queryCommond.getSearchDepId());
		}
		//单位id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())){
			values.put("deptid", queryCommond.getSearchDepId());
		}
		//报名机构
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDataCode())){
			queryHql.append(" or sn.dataCode =:dataCode) ");
			values.put("dataCode", queryCommond.getSearchDataCode());
		}
		//培训班类别
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassCategory())){
			queryHql.append(" and t.classCategory =:classCategory ");
			values.put("classCategory", queryCommond.getSearchClassCategory());
		}
		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			queryHql.append(" and t.classMainName like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().replace(" ", "")+"%");
		}
		//培训班类型查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())) {
			queryHql.append(" and ct.dataCode =:classType ");
			values.put("classType", queryCommond.getSearchClassType());
		}
		//主办单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName())) {
			queryHql.append(" and t.organizerName like :organizerName ");
			values.put("organizerName", "%"+queryCommond.getSearchOrganizerName().replace(" ", "")+"%");
		}
		//报名开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEnterBeginTime())) {
			queryHql.append(" and t.enterBeginTime between :enterBeginTime and :enterBeginTime1 ");
			values.put("enterBeginTime", DateUtil.getDateByString(queryCommond.getSearchEnterBeginTime()+" 00:00:00", "yyyy-MM-dd HH:mm:ss"));
			values.put("enterBeginTime1", DateUtil.getDateByString(queryCommond.getSearchEnterBeginTime()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		//报名截止时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEnterEndTime())){
			queryHql.append(" and t.enterEndTime between :enterEndTime and :enterEndTime1");
			values.put("enterEndTime", DateUtil.getDateByString(queryCommond.getSearchEnterEndTime()+" 00:00:00", "yyyy-MM-dd HH:mm:ss"));
			values.put("enterEndTime1", DateUtil.getDateByString(queryCommond.getSearchEnterEndTime()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			queryHql.append(" and t.activeState =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}
		//班级id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			queryHql.append(" and t.classId = :classId");
			values.put("classId", queryCommond.getSearchClassId());
		}
		//拼写查询条件sql end

		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
		.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		countHql.append(queryHql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		//
		int firstResult = queryCommond.calculate(count.longValue());

		// 数据列表查询
		hql.append(queryHql);

		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			if(sortInfo.getProperty().equals("t.reportedNum")) {
			}else {
				hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			}
		} else {
			hql.append(" order by t.createDate desc");//modify by dhc @2014-04-14
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);

		return (List<TrainingClassQueryBean>) this.findList(queryCondition1,
				firstResult, queryCommond.getLimit());
	}

	/**
	 * 查询单位上报市级项目审核
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @date 2014-1-7
	 */
	public List<TrainingClassQueryBean> findTrainingClassListForAuditDS(
			TrainingClassQueryCommond queryCommond) throws Exception
			{
		StringBuffer hql=new StringBuffer();
		hql.append("select * from (" +
				" select t.CLASS_ID as classId,t.AUDIT_RESULT as auditState,t.REPORT_DATE as reportDate,max(cai.CREATE_DATE) as auditingDate," +
				" t.CLASS_MAIN_NAME as className,bb.DATA_NAME as classType," +
				" t.ORGANIZER_NAME as organizerName,t.TRAINING_DAY as trainingDay,t.TRAINING_HOUR as trainingHour," +
				" t.BEGIN_DATE as beginDate,t.END_DATE as endDate," +
				" (" +
				" select count(tcu.CLASS_USER_ID) " +
				" from TRAINING_CLASS_USER tcu where tcu.CLASS_ID=t.CLASS_ID " +
				" and tcu.AUDITING_STATE=1 and tcu.AUDITING_HOURS_STATE=3 and tcu.REPORTED_HOURS_STATE=2" +
				" ) as noAuditHoursBig," +
				//添加查询上报单位
				" t.REPORT_DEP_NAME as reportDepName" +
				" from training_class t left join base_basedata bb on t.CLASS_TYPE=bb.DATA_CODE " +
				" left join class_audit_info cai on t.CLASS_ID=cai.CLASS_ID where 1=1 ");
		Map<String, Object> params=new HashMap<String, Object>();
		//培训班类别
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassCategory()))
		{
			hql.append(" and t.CLASS_CATEGORY = :classCategory");
			params.put("classCategory", queryCommond.getSearchClassCategory());
		}
		//		//项目上报状态
		//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchReportStatus()))
		//		{
		//			hql.append(" and t.REPORT_STATE = :reportState");
		//			params.put("reportState", queryCommond.getSearchReportStatus());
		//		}
		//培训班名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName()))
		{
			hql.append(" and t.CLASS_MAIN_NAME like :className");
			params.put("className", "%"+queryCommond.getSearchClassName().replace(" ", "")+"%");
		}
		//培训班类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType()))
		{
			hql.append(" and t.CLASS_TYPE = :classType");
			params.put("classType", queryCommond.getSearchClassType());
		}
		//主办单位ID(包括当前单位的所有下属单位)
		if(PropertyUtil.objectNotEmpty(queryCommond.getTreePath()))
		{
			hql.append(" and t.ORGANIZER_ID in (select bd.DEPID from base_department bd where bd.TREEPATH like :treePath)");
			params.put("treePath", queryCommond.getTreePath()+"%");
		}
		//主办单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName()))
		{
			hql.append(" and t.ORGANIZER_NAME like :organizerName");
			params.put("organizerName", "%"+queryCommond.getSearchOrganizerName().replace(" ", "")+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate()))
		{
			hql.append(" and t.BEGIN_DATE like :beginDate");
			params.put("beginDate", queryCommond.getSearchBeginDate()+"%");
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate()))
		{
			hql.append(" and t.END_DATE like :endDate");
			params.put("endDate", queryCommond.getSearchEndDate()+"%");
		}
		//项目审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState()))
		{
			hql.append(" and t.AUDIT_RESULT = :auditState");
			params.put("auditState", queryCommond.getSearchAuditState());
		}

		hql.append(" group by t.CLASS_ID) as tc");
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				hql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					hql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		else
		{
			hql.append(" order by auditState DESC,reportDate ASC");
		}

		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);

		List<TrainingClassQueryBean> findList = new ArrayList<TrainingClassQueryBean>();

		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(TrainingClassQueryBean.class);
		queryCondition.setResultMapper(mapper);

		if(queryCommond.getPage()==-1){
			findList = (List<TrainingClassQueryBean>) this.findList(queryCondition);
		}else{
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<TrainingClassQueryBean>) this.findList(queryCondition,
					firstResult, queryCommond.getLimit());
		}

		return findList;
			}
	/**
	 * 向干教网同步数据查询培训项目列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @date 2014-1-9
	 */
	public List<SyncTrainingClassQueryBean> findTrainingClassListSyncToGJW(
			TrainingClassQueryCommond queryCommond) throws Exception
			{
		StringBuffer hql=new StringBuffer("");
		Map<String, Object> params=new HashMap<String, Object>();
		hql.append("select * from (");
		hql.append("select t_1.CLASS_ID as classId,t_1.CLASS_MAIN_NAME as className,bb.DATA_NAME as classType," +
				" t_1.ORGANIZER_ID as organizerId,t_1.ORGANIZER_NAME as organizerName," +
				" t_1.BEGIN_DATE as beginDate,t_1.END_DATE as endDate," +
				" t_1.TRAINING_DAY as trainingDay,t_1.TRAINING_HOUR as trainingHour,t_1.CLASS_USER_NUM as classUserNum," +
				" (" +
				" select count(tcu.CLASS_USER_ID) from training_class_user tcu" +
				" where tcu.CLASS_ID = t_1.CLASS_ID and tcu.AUDITING_STATE = " + ClassUser.IS_AUDITING_Y +
				" ) as reportedNumBig," +
				" t_1.SYNC_CLASS_STATE as syncClassState,t_1.SYNC_USER_STATE as syncUserState," +
				" t_1.SYNC_TRAINING_HOURS_STATE as syncTrainingHoursState" +
				" from (" +
				" SELECT * from training_class tc where tc.CLASS_CATEGORY='"+TrainingClass.D_CLASS_TYPE+"' and tc.AUDIT_RESULT="+TrainingClass.IS_AUDIT_Y+" and tc.ACTIVE_STATE="+ TrainingClass.IS_ACTIVE_Y +
				" UNION" +
				" SELECT * from training_class tc where tc.CLASS_CATEGORY='"+TrainingClass.Q_CLASS_TYPE+"' and tc.APPLY_WAY='"+TrainingClass.APPLY_WAY_DMTX+"' and tc.ACTIVE_STATE="+ TrainingClass.IS_ACTIVE_Y +
				" UNION" +
				" SELECT * from training_class tc where tc.CLASS_CATEGORY='"+TrainingClass.Q_CLASS_TYPE+"' and tc.APPLY_WAY not IN('"+TrainingClass.APPLY_WAY_DMTX+"') and tc.PUBLISH_STATE="+TrainingClass.IS_PUBLISH_Y + " and tc.ACTIVE_STATE="+ TrainingClass.IS_ACTIVE_Y +
				" ) as t_1" +
				" left join base_basedata bb on t_1.CLASS_TYPE=bb.DATA_CODE " +
				" left join base_department bd on t_1.ORGANIZER_ID=bd.DEPID" +
				" where 1=1");
		//培训班名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName()))
		{
			hql.append(" and t_1.CLASS_MAIN_NAME like :className");
			params.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//培训班类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType()))
		{
			hql.append(" and t_1.CLASS_TYPE = :classType");
			params.put("classType", queryCommond.getSearchClassType());
		}
		//主办单位ID
		//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerId())&&!queryCommond.getSearchOrganizerId().equals("1854247"))
		//		{
		//			hql.append(" and t_1.ORGANIZER_ID = :organizerId");
		//			params.put("organizerId", queryCommond.getSearchOrganizerId());
		//		}
		//根据treePath，包含下级单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerId()))
		{
			hql.append(" and bd.TREEPATH like :treePath");
			params.put("treePath", queryCommond.getTreePath()+"%");
		}
		//主办单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName()))
		{
			hql.append(" and t_1.ORGANIZER_NAME like :organizerName");
			params.put("organizerName", "%"+queryCommond.getSearchOrganizerName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate()))
		{
			hql.append(" and t_1.BEGIN_DATE like :beginDate");
			params.put("beginDate", queryCommond.getSearchBeginDate()+"%");
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate()))
		{
			hql.append(" and t_1.END_DATE like :endDate");
			params.put("endDate", queryCommond.getSearchEndDate()+"%");
		}
		hql.append(" order by t_1.create_date desc");
		hql.append(") as t");
		//		hql.append(" order by tc.create_date desc ) as t");//modify by dhc 默认按创建时间降序排列 
		// 增加排序
		if (queryCommond.getSortInfos() != null) 
		{
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) 
			{
				hql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) 
				{
					hql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}

		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<SyncTrainingClassQueryBean> findList = new ArrayList<SyncTrainingClassQueryBean>();

		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(SyncTrainingClassQueryBean.class);
		queryCondition.setResultMapper(mapper);

		if(queryCommond.getPage()==-1){
			findList = (List<SyncTrainingClassQueryBean>) this.findList(queryCondition);
		}else{
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<SyncTrainingClassQueryBean>) this.findList(queryCondition,
					firstResult, queryCommond.getLimit());
		}

		return findList;
			}
	/**
	 * 更新班级表项目审核时间
	 * 
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-1-17
	 */
	public void updateAduitDate(String classId,String aduitDate) throws Exception
	{
		String sql="update training_class tc set tc.AUDITING_DATE = :aduitDate where tc.CLASS_ID = :classId";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("aduitDate", aduitDate);
		params.put("classId", classId);
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	/**
	 *根据用户id查询用户部门id
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2014-4-12
	 */
	@Override
	public List<DepartmentUserBean> getDepIdByUserId(String[] userId) throws Exception {
		StringBuffer hql = new StringBuffer();
		Map<String, Object> values=new HashMap<String, Object>();
		hql.append("SELECT t.department.deptID as depId,t.user.userId as userId FROM DepartmentUser t where 1=1");
		if(PropertyUtil.objectNotEmpty(userId)){
			hql.append(" and t.user.userId in (:userId)");
			values.put("userId", userId);
		}
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
		.setResultObject(DepartmentUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<DepartmentUserBean> list=(List<DepartmentUserBean>)this.findList(queryCondition);
		return list;
	}


	/**
	 * 通过excle导入的班级用户，此方法别的地方不要调用
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-5-14
	 */
	@Override
	public void addUserByExcel(TrainingClass trainingClass,List<String> userIDs)throws Exception {
		Session session =null;
		Connection conn=null;
		PreparedStatement pst=null;
		boolean succ=false;
		try {
			session=this.getSession();
			if(userIDs!=null&&userIDs.size()>0) {
				StringBuffer sql = new StringBuffer();
				sql.append("insert into TRAINING_CLASS_USER ");
				sql.append(" (CLASS_USER_ID,CLASS_ID,TRAINING_HOURS,GJ_SYNC_HOURS_STATE,GRADUATE_STATE,");
				sql.append(" AUDITING_STATE,GJ_SYNC_USER_STATE,GBK_SYNC_USER_STATE,ACTIVE_STATE,CREATE_DATE,USER_ID)");
				sql.append(" values (?,?,?,?,?,?,?,?,?,?,?)");
				conn = session.connection();
				conn.setAutoCommit(false);
				pst = conn.prepareStatement(sql.toString());
				for(int i=0;i<userIDs.size();i++){
					String userid=userIDs.get(i); 
					pst.setString(1, UUID.randomUUID().toString().replace("-", ""));
					pst.setString(2, trainingClass.getClassId());
					pst.setDouble(3, trainingClass.getTrainingHour());
					pst.setInt(4, 1);
					pst.setInt(5, 1);
					pst.setInt(6, 1);
					pst.setInt(7, 1);
					pst.setInt(8, 1);
					pst.setInt(9, 1);
					pst.setDate(10, new java.sql.Date(new Date().getTime()));
					pst.setString(11, userid);
					pst.addBatch();
				}
				pst.executeBatch();
				conn.commit();
				succ=true;
			}else {
				succ=true;
			}
		}catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
			throw e;
		} finally {
			if (pst != null) {
				pst.close();
			}
			if (conn != null) {
				conn.close();
			}
			this.getSession().close();
		}
	}


	/**
	 * 通过excle导入的班级用户中在线学习的信息，此方法别的地方不要调用
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-5-14
	 */
	@Override
	public void addUserOnLineInfoByExcel(List<UserIdAndYear> doUserIDs)throws Exception {

		Session session =null;
		Connection conn=null;
		PreparedStatement pst=null;
		boolean succ=false;
		try {
			session=this.getSession();
			if(doUserIDs!=null&&doUserIDs.size()>0) {
				StringBuffer sql = new StringBuffer();
				sql.append("insert into USER_STUDYHOUR_INFO ");
				sql.append(" (ID,USERID,YEAR,STUDY_HOUR,TRAIN_DEPT,");
				sql.append(" TRAIN_DATE,ACTIVE_STATE,AUDIT_STATE)");
				sql.append(" values (?,?,?,?,?,?,?,?)");
				conn = session.connection();
				conn.setAutoCommit(false);
				//分割集合
				List splitList=this.splitList(doUserIDs);

				//				 for( int z=0;z<splitList.size();z++) {
				//					 List<UserIdAndYear> templist=(List<UserIdAndYear>) splitList.get(z);
				pst = conn.prepareStatement(sql.toString());
				for(int i=0;i<doUserIDs.size();i++){
					UserIdAndYear uy=doUserIDs.get(i); 
					pst.setString(1, UUID.randomUUID().toString().replace("-", ""));
					pst.setString(2, uy.getUserID());
					pst.setString(3, uy.getStartYear().toString());
					pst.setDouble(4, 60);
					pst.setString(5, "111111");
					pst.setString(6, "1-12");

					pst.setInt(7, 1);
					pst.setInt(8, 1);
					pst.addBatch();
				}
				pst.executeBatch();

				conn.commit();
				succ=true;
				// }
			}else {
				succ=true;
			}
		}catch (Exception e) {
			conn.rollback();
			e.printStackTrace();
			throw e;
		} finally {
			if (pst != null) {
				pst.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
	}

	/*
	 * 查询人员信息
	 */
	@Override
	public Map<String,String> getUserIDInfo() throws Exception {

		String sql="select u.uid as uid,u.name as name,u.birth as birth from u_info u";
		TrainingClassQueryCondition condition=new TrainingClassQueryCondition();
		condition.setQueryString(sql);
		BeanResultMapper mapper=(BeanResultMapper)ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(UserInfoBean.class);
		condition.setResultMapper(mapper);
		condition.setQueryStringType(QueryStringType.SQL);
		List<UserInfoBean> list=(List<UserInfoBean>)this.findList(condition, -1, -1);

		Map<String,String> map=new HashMap<String,String>();

		for(int i=0;i<list.size();i++) {
			UserInfoBean bean=list.get(i);
			String name=bean.getName().replace("　", "");
			String birth=bean.getBirth();
			String uid=bean.getUid();
			map.put(name+"+"+birth, uid);
		}
		return map;
	}

	/**
	 * 获取这一年中培训班最大的编号
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-09
	 */
	@Override
	public String getTrainingNum(TrainingClassValidCommond vCommond) throws Exception{
		Map<String, Object> values=new HashMap<String, Object>();
		String hql="select max(tc.classNum) as classMaxNum from TrainingClass tc where tc.classNum like :classNum and tc.classNum is not null and tc.activeState=:activeState";
		values.put("classNum", "%"+vCommond.getCurrYear()+vCommond.getTrainingDeptCode()+"%");
		values.put("activeState", TrainingClass.IS_ACTIVE_Y);
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<Object> result= (List<Object>) this.findList(queryCondition);
		if(result.get(0)!=null) {
			return result.get(0).toString();
		}else {
			return "empty";
		}
	}

	/**
	 * 分割List 方法
	 * @param commond
	 * @return  boolean
	 * @throws Exception
	 * @author Max
	 * @date 2013-8-23
	 */
	public List<List> splitList(List list) {
		List<List> lastList=new ArrayList<List>();
		int size = list.size() % 1000;
		if (size == 0) {
			size = list.size() / 1000;
		} else {
			size = (list.size() /1000) + 1;
		}

		for(int i=0;i<size;i++) {
			if(i==size-1) {
				lastList.add(list.subList(i*1000, i*1000+(list.size()-i*1000)));
			}else {
				lastList.add(list.subList(i*1000, i*1000+1000));
			}
		}
		return lastList;
	}

	@Override
	public void addTrainingClassByExcel(TrainingClass trainingClass)throws Exception {
		Session session =null;
		try {
			session=this.getSession();
			session.save(trainingClass);
		}catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			session.close();
		}
	}


	/**
	 * 更新班级表学时
	 * 
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-15
	 */
	@Override
	public void updateStudyHour(Double hour,String classId) throws Exception
	{
		String sql="update training_class tc set tc.TRAINING_HOUR = :hour where tc.CLASS_ID = :classId";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("hour", hour);
		params.put("classId", classId);
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	/**
	 * 查询班级列表(不分页)
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @date 2014-12-16
	 */
	@Override
	public List<TrainingClassQueryBean> findTrainingClassListNoPage(
			TrainingClassQueryCommond queryCommond) throws Exception {
		StringBuffer queryHql = new StringBuffer();
		//拼写查询的sql
		StringBuffer hql = new StringBuffer();

		hql.append("select t.classDescription as classDescription,t.sponsorName as sponsorName,t.classLevel as classLevel,t.classId as classId, t.reportState as reportState,t.auditState as auditState,t.classUserNum as classUserNum,t.ministrieName as ministrieName," +
				"t.openState as openState, t.className as className, t.classNum as classNum,t.enterBeginTime as enterBeginTime,t.enterEndTime as enterEndTime," +
				"t.beginDate as beginDate,t.endDate as endDate,t.trainingDeptName as trainingDeptName,t.trainingHour as trainingHour,");
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5)) as reportedNum,");//已报人数,审核通过人数
		//hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId) as reportedNumber,");//已报人数,包括审核通过、审核未通过、未审核
		//全勤人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5) and cu.attendanceState=1) as allAttendance,");
		//缺勤人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5) and cu.attendanceState=2) as absenceAttendance,");
		//请假人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5) and cu.attendanceState=3) as leaveAttendance,");
		//未出勤人数
		hql.append(" (select count(cu.classUserId) from ClassUser cu where cu.trainingClass.classId=t.classId and cu.auditingState in (1,5) and cu.attendanceState=4) as noAttendance,");
		hql.append(" t.publishState as publishState,t.activeState as activeState");
		hql.append(" FROM TrainingClass t left join t.classType ct left join t.applyWay aw where 1=1");
		//查询数量的sql	
		StringBuffer countHql = new StringBuffer("FROM TrainingClass t left join t.classType ct where 1=1 ");

		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		//培训机构id查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieID())){
			queryHql.append(" and t.ministrieID like :ministrieID ");
			values.put("ministrieID", queryCommond.getSearchMinistrieID().trim());
		}
		
		//班级id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIdList())){
			queryHql.append(" and t.classId in (:classId) ");
			values.put("classId", queryCommond.getSearchClassIdList());
		}

		//培训机构id查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())){
			queryHql.append(" and t.trainingDeptId like :trainingDeptId ");
			values.put("trainingDeptId", queryCommond.getSearchTrainingDeptId().trim());
		}

		//培训机构编号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())){
			queryHql.append(" and t.classNum =:classNum ");
			values.put("classNum",queryCommond.getSearchTrainingNum().trim());
		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			queryHql.append(" and t.classType.dataCode = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}
		//培训机构名称查询
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())){
			queryHql.append(" and t.trainingDeptName like :trainingDeptName ");
			values.put("trainingDeptName", "%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}		
		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			queryHql.append(" and t.className like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//项目上报状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchReportState())) {
			queryHql.append(" and t.reportState = :reportState");
			values.put("reportState", queryCommond.getSearchReportState());
		}

		//项目审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState()))
		{
			queryHql.append(" and t.auditState = :auditState");
			values.put("auditState", queryCommond.getSearchAuditState());
		}
		//主办单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName())) {
			queryHql.append(" and t.organizerName like :organizerName ");
			values.put("organizerName", "%"+queryCommond.getSearchOrganizerName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			queryHql.append(" and t.beginDate >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndTime())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.endDate <=:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndTime().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNowDate())){
			queryHql.append(" and t.endDate <=:endDate ");
			values.put("endDate", new Date());
		}

		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			queryHql.append(" and t.openState =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			queryHql.append(" and t.endState =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}

		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			queryHql.append(" and t.publishState =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			queryHql.append(" and t.activeState =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}
		//专题班报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())){
			queryHql.append(" and t.signUpState =:signupState ");
			values.put("signupState", queryCommond.getSearchSignUpState());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId()))
		{
			queryHql.append(" and t.classId = :classId");
			values.put("classId", queryCommond.getSearchClassId().trim());
		}
		//承办部委
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieName())) {
			queryHql.append(" and t.ministrieName like :ministrieName");
			values.put("ministrieName", "%"+queryCommond.getSearchMinistrieName().trim()+"%");
		}
		//拼写查询条件sql end

		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder
		.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		countHql.append(queryHql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		//
		int firstResult = queryCommond.calculate(count.longValue());

		// 数据列表查询
		hql.append(queryHql);

		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			if(queryCommond.getSearchType()!=null&&queryCommond.getSearchType().equals("attedence")){
				//如果是考勤导出列表 结束日期升序排列，结束早的排上面，结束晚的排下面 by Max at 2015-04-07
				hql.append(" order by t.endDate ");
			}else {
				hql.append(" order by t.createDate desc");
			}
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);

		return (List<TrainingClassQueryBean>) this.findList(queryCondition1);
	}

	public static void main(String []args) {
		Integer i=0;
		try {
			System.out.println(2/i);
		}catch (Exception e){
			e.printStackTrace();
		}
		System.out.println(1);
	}
	/**
	 * 单位报名班级列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @date 2015-1-5
	 */
	@Override
	public List<TrainingClassQueryBean> findTrainingClassListByDept(
			TrainingClassQueryCommond queryCommond) throws Exception {
		//拼写查询的sql
		StringBuffer querySql = new StringBuffer();//查询条件
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct r.class_id as \"classId\",r.signup_state as \"signUpState\",r.class_user_num as \"classUserNum\",r.open_state as \"openState\",r.end_state as \"endState\"," +
				"r.class_main_name as \"className\",r.class_num as \"classNum\",r.training_dept_name as \"trainingDeptName\",r.training_hour as \"trainingHour\",r.enter_begin_time as \"enterBeginTime\"," +
				"r.enter_end_time as \"enterEndTime\",r.class_type as \"classTypeCode\",r.allAttendance as \"allAttendance\",r.absenceAttendance as \"absenceAttendance\",r.leaveAttendance as \"leaveAttendance\",r.noAttendance as \"noAttendance\",r.data_name as \"classTypeStr\",r.countUserNum as \"countUserNum\",r.depUserNum as \"depUserNum\",r.create_date as \"createDate\"," +
				"r.begin_date as \"beginDate\",r.end_date as \"endDate\" from (select tc.*,bb.data_name,");
		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+") as countUserNum,");//已报人数

		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.attendance_state = 1) as allAttendance,");//全勤人数
		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.attendance_state = 2) as absenceAttendance,");//缺勤人数
		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.attendance_state = 3) as leaveAttendance,");//请假人数
		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.attendance_state = 4) as noAttendance,");//未出勤人数
		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid " +
				"left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" " +
				" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and bu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and sd.treepath like :treepath1) as depUserNum ");//本单位已报人数
		sql.append("  from training_class tc left join training_class_dept cd on tc.class_id = cd.class_id left join base_department ssd  " +
				" on cd.depid = ssd.depid left join base_basedata bb on tc.class_type = bb.data_code where 1 = 1 ");
		//查询数量的sql	
		StringBuffer countHql = new StringBuffer("select r.class_id from (select distinct tc.class_id from training_class tc left join training_class_dept cd " +
				"on tc.class_id=cd.class_id left join signup_dept ssd on cd.depid = ssd.depid where 1=1 ");
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		//报名单位id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingApplyDeptId())){
			querySql.append(" and (ssd.treepath like :treepath2 or tc.class_type =:classType) ");
			values.put("treepath1", "%"+queryCommond.getSearchTrainingApplyDeptTreepath().trim()+"%");
			values.put("treepath2", "%"+queryCommond.getSearchTrainingApplyDeptTreepath().trim()+"%");
			values.put("classType", TrainingClass.PTB_CLASS_TYPE);
		}
		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			querySql.append(" and tc.publish_state =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			querySql.append(" and tc.active_state =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}
		// 专题班审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getAuditState())){
			querySql.append(" and tc.audit_state =:auditState ");
			values.put("auditState", queryCommond.getAuditState());
		}
		//班级编号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			querySql.append(" and tc.class_num like :classNum ");
			values.put("classNum", "%"+queryCommond.getSearchTrainingNum().trim()+"%");
		}

		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			querySql.append(" and tc.class_main_name like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			querySql.append(" and tc.begin_date >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			querySql.append(" and tc.end_date <=:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
		}
		//培训机构id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())) {
			querySql.append(" and tc.training_dept_id = :trainingDeptId  ");
			values.put("trainingDeptId",queryCommond.getSearchTrainingDeptId());
		}
		//培训机构名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())) {
			querySql.append(" and tc.training_dept_name like :trainingDeptName  ");
			values.put("trainingDeptName","%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			querySql.append(" and tc.CLASS_TYPE = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			querySql.append(" and tc.Signup_State = :signUpState  ");
			values.put("signUpState",queryCommond.getSearchSignUpState());
		}
		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			querySql.append(" and tc.open_state =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			querySql.append(" and tc.end_state =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}
		//拼写查询条件sql end
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder
		.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		countHql.append(querySql+") r");
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		// 数据列表查询
		sql.append(querySql+") r");
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			sql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			sql.append(" order by r.create_date desc");
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		return (List<TrainingClassQueryBean>) this.findList(queryCondition1,
				firstResult, queryCommond.getLimit());
	}



	/**
	 * 单位报名班级列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author GaoGQ
	 * @date 2015-03-26
	 */
	@Override
	public List<TrainingClassQueryBean> findTrainingClassListByDeptFinish(
			TrainingClassQueryCommond queryCommond) throws Exception {
		//拼写查询的sql
		StringBuffer querySql = new StringBuffer();//查询条件
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct r.class_id as \"classId\",r.signup_state as \"signUpState\",r.class_user_num as \"classUserNum\",r.open_state as \"openState\",r.end_state as \"endState\"," +
				"r.class_main_name as \"className\",r.class_num as \"classNum\",r.training_dept_name as \"trainingDeptName\",r.training_hour as \"trainingHour\",r.enter_begin_time as \"enterBeginTime\"," +
				"r.enter_end_time as \"enterEndTime\",r.class_type as \"classTypeCode\",r.data_name as \"classTypeStr\",r.countUserNum as \"countUserNum\",r.allAttendance as \"allAttendance\",r.absenceAttendance as \"absenceAttendance\",r.leaveAttendance as \"leaveAttendance\",r.noAttendance as \"noAttendance\",r.depUserNum as \"depUserNum\",r.create_date as \"createDate\"," +
				"r.begin_date as \"beginDateNoYear\",r.end_date as \"endDateNoYear\" from (select tc.*,bb.data_name,");
		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+") as depUserNum,");//已报人数

		/*sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.ATTENDANCE_STATE=1) as allAttendance,");//全勤人数

		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.ATTENDANCE_STATE=2) as absenceAttendance,");//缺勤人数

		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.ATTENDANCE_STATE=3) as leaveAttendance,");//请假人数

		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.ATTENDANCE_STATE=4) as noAttendance,");//未出勤人数
		 */		

		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid " +
				"left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" " +
				" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and bu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and sd.treepath like :treepath1) as countUserNum ,");//本单位已报人数


		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid " +
				"left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" " +
				" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and bu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.attendance_state = 1 and sd.treepath like :treepath1) as allAttendance ,");//本单位全勤


		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid " +
				"left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" " +
				" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and bu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.attendance_state = 2 and sd.treepath like :treepath1) as absenceAttendance, ");//本单位缺勤


		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid " +
				"left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" " +
				" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and bu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.attendance_state = 3 and sd.treepath like :treepath1) as leaveAttendance ,");//本单位请假


		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid " +
				"left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" " +
				" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and bu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.attendance_state = 4 and sd.treepath like :treepath1) as noAttendance ");//本单位未出勤


		sql.append("  from training_class tc left join training_class_dept cd on tc.class_id = cd.class_id left join base_department ssd  " +
				" on cd.depid = ssd.depid left join base_basedata bb on tc.class_type = bb.data_code where 1 = 1 ");
		//查询数量的sql	
		StringBuffer countHql = new StringBuffer("select r.class_id from (select distinct tc.class_id from training_class tc left join training_class_dept cd " +
				"on tc.class_id=cd.class_id left join signup_dept ssd on cd.depid = ssd.depid where 1=1 ");
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		//报名单位id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingApplyDeptId())){
			querySql.append(" and (ssd.treepath like :treepath2 or tc.class_type =:classType) ");
			values.put("treepath1", "%"+queryCommond.getSearchTrainingApplyDeptTreepath().trim()+"%");
			values.put("treepath2", "%"+queryCommond.getSearchTrainingApplyDeptTreepath().trim()+"%");
			values.put("classType", TrainingClass.PTB_CLASS_TYPE);
		}
		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			querySql.append(" and tc.publish_state =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			querySql.append(" and tc.active_state =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}
		// 专题班审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getAuditState())){
			querySql.append(" and tc.audit_state =:auditState ");
			values.put("auditState", queryCommond.getAuditState());
		}
		//班级编号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			querySql.append(" and tc.class_num like :classNum ");
			values.put("classNum", "%"+queryCommond.getSearchTrainingNum().trim()+"%");
		}

		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			querySql.append(" and tc.class_main_name like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			querySql.append(" and tc.begin_date >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}


		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			querySql.append(" and tc.end_date <=:enter_end_time");
			values.put("enter_end_time", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
		}
		//培训机构id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())) {
			querySql.append(" and tc.training_dept_id = :trainingDeptId  ");
			values.put("trainingDeptId",queryCommond.getSearchTrainingDeptId());
		}
		//培训机构名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())) {
			querySql.append(" and tc.training_dept_name like :trainingDeptName  ");
			values.put("trainingDeptName","%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			querySql.append(" and tc.CLASS_TYPE = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			querySql.append(" and tc.Signup_State = :signUpState  ");
			values.put("signUpState",queryCommond.getSearchSignUpState());
		}
		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			querySql.append(" and tc.open_state =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			querySql.append(" and tc.end_state =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}
		//拼写查询条件sql end
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder
		.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		countHql.append(querySql+ "and (select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid " +
				"left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" " +
				" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and bu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and sd.treepath like :treepath1)>0   ) r ");
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		// 数据列表查询
		//之查询有报名数据的班级  by Max at 2015-04-07
		sql.append(querySql+") r where r.countUserNum>0 ");
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			sql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			//培训班结束日期降序排列，即结束晚的排上面，结束早的拍下面  by Max at 2015-04-07
			sql.append(" order by r.end_date desc, r.create_date desc");
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		return (List<TrainingClassQueryBean>) this.findList(queryCondition1,
				firstResult, queryCommond.getLimit());
	}
	/**
	 *  查询单位培训班没有分页
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author ShenPS
	 * @date 2015年1月15日
	 */
	public List<TrainingClassQueryBean> findTrainingClassListByDeptNoPage(
			TrainingClassQueryCommond queryCommond) throws Exception {
		//拼写查询的sql
		StringBuffer querySql = new StringBuffer();//查询条件
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct r.class_id as \"classId\",r.signup_state as \"signUpState\",r.class_user_num as \"classUserNum\",r.open_state as \"openState\",r.end_state as \"endState\"," +
				"r.CLASS_NAME as \"className\",r.class_num as \"classNum\",r.training_dept_name as \"trainingDeptName\",r.training_hour as \"trainingHour\",r.enter_begin_time as \"enterBeginTime\"," +
				"r.enter_end_time as \"enterEndTime\",r.class_type as \"classTypeCode\",r.data_name as \"classTypeStr\",r.countUserNum as \"countUserNum\",r.depUserNum as \"depUserNum\",r.create_date as \"createDate\"," +
				"r.begin_date as \"beginDate\",r.allAttendance as \"allAttendance\",r.absenceAttendance as \"absenceAttendance\",r.leaveAttendance as \"leaveAttendance\", r.noAttendance as \"noAttendance\", r.end_date as \"endDate\" from (select tc.*,bb.data_name,");
		sql.append("(select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+") as countUserNum,");//已报人数
		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid left join base_department sd on bu.depid = sd.depid  where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.attendance_state = 1 and sd.treepath like :treepath1) as allAttendance,");//全勤人数
		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.attendance_state = 2 and sd.treepath like :treepath1) as absenceAttendance,");//缺勤人数
		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.attendance_state = 3 and sd.treepath like :treepath1) as leaveAttendance,");//请假人数
		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id " +
				"and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and cu.attendance_state = 4 and sd.treepath like :treepath1) as noAttendance,");//未出勤人数
		sql.append("(select count(cu.class_user_id)  from training_class_user cu left join base_department_user bu on cu.user_id = bu.userid " +
				"left join base_department sd on bu.depid = sd.depid where cu.class_id = tc.class_id and cu.active_state="+TrainingConstants.IS_ACTIVE_Y+" " +
				" and cu.auditing_state="+ClassUser.IS_AUDITING_Y+" and bu.active_state="+TrainingConstants.IS_ACTIVE_Y+" and sd.treepath like :treepath1) as depUserNum ");//本单位已报人数
		sql.append("  from training_class tc left join training_class_dept cd on tc.class_id = cd.class_id left join base_department ssd  " +
				" on cd.depid = ssd.depid left join base_basedata bb on tc.class_type = bb.data_code where 1 = 1 ");
		//查询数量的sql	
		//		StringBuffer countHql = new StringBuffer("select r.class_id from (select distinct tc.class_id from training_class tc left join training_class_dept cd " +
		//				"on tc.class_id=cd.class_id left join signup_dept ssd on cd.depid = ssd.depid where 1=1 ");
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();
		//报名单位id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingApplyDeptId())){
			querySql.append(" and (ssd.treepath like :treepath2 or tc.class_type =:classType) ");
			values.put("treepath1", "%"+queryCommond.getSearchTrainingApplyDeptTreepath().trim()+"%");
			values.put("treepath2", "%"+queryCommond.getSearchTrainingApplyDeptTreepath().trim()+"%");
			values.put("classType", TrainingClass.PTB_CLASS_TYPE);
		}
		//班级id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIdList())){
			querySql.append(" and tc.class_id in (:classId) ");
			values.put("classId", queryCommond.getSearchClassIdList());
		}
		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			querySql.append(" and tc.publish_state =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			querySql.append(" and tc.active_state =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}
		// 专题班审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getAuditState())){
			querySql.append(" and tc.audit_state =:auditState ");
			values.put("auditState", queryCommond.getAuditState());
		}
		//班级编号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			querySql.append(" and tc.class_num like :classNum ");
			values.put("classNum", "%"+queryCommond.getSearchTrainingNum().trim()+"%");
		}
		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			querySql.append(" and tc.class_main_name like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			querySql.append(" and tc.begin_date >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			querySql.append(" and tc.end_date <=:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
		}
		//培训机构id
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())) {
			querySql.append(" and tc.training_dept_id = :trainingDeptId  ");
			values.put("trainingDeptId",queryCommond.getSearchTrainingDeptId());
		}
		//培训机构名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())) {
			querySql.append(" and tc.training_dept_name like :trainingDeptName  ");
			values.put("trainingDeptName","%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			querySql.append(" and tc.Signup_State = :signUpState  ");
			values.put("signUpState",queryCommond.getSearchSignUpState());
		}
		// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			querySql.append(" and tc.open_state =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			querySql.append(" and tc.end_state =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			querySql.append(" and tc.CLASS_TYPE = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}
		//拼写查询条件sql end
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder
		.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		//		countHql.append(querySql+") r");
		//		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		//		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		//		queryCondition.setParameters(values);
		//查询总数量
		//		Long count = this.count(queryCondition);
		//		int firstResult = queryCommond.calculate(count.longValue());
		// 数据列表查询
		sql.append(querySql+") r");
		//拼写排序的sql begin
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			sql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			if(queryCommond.getSearchType()!=null&&queryCommond.getSearchType().equals("attedence")){
				//如果是考勤导出列表 结束日期升序排列，结束早的排上面，结束晚的排下面 by Max at 2015-04-07
				sql.append(" order by r.end_date ");
			}else {
				sql.append(" order by r.create_date desc");
			}
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		return (List<TrainingClassQueryBean>) this.findList(queryCondition1);
	}


	/**
	 *更新状态
	 * 
	 * @throws Exception
	 * @author Max
	 * @createDate 2014-12-15
	 */
	@Override
	public void updateCState(String classId) throws Exception {
		String sql="update training_class tc set tc.AUDIT_STATE ='"+TrainingClass.IS_N_AUDIT+"' ,tc.REPORT_STATE='"+TrainingClass.IS_REPORT_N+"',tc.AUDIT_DATE = null,tc.REPORT_DATE = null where tc.CLASS_ID = :classId";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("classId", classId);
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * 查询学员查看的班级列表
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author ShenPS
	 * @date 2015年1月12日
	 */
	@Override
	public List<TrainingClassQueryBean> findUserSignupClassList(
			TrainingClassQueryCommond queryCommond) throws Exception {

		Map<String, Object> values = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		StringBuffer countSql = new StringBuffer();
		StringBuffer queryField = new StringBuffer();
		StringBuffer countField = new StringBuffer();
		StringBuffer querySql1 = new StringBuffer();//第一个sql查询条件
		StringBuffer querySql2 = new StringBuffer();//第二个sql查询条件
		StringBuffer querySql = new StringBuffer();//公有的查询条件	
		countSql.append("select count(*) as count from ");

		countField.append(" select c.class_id ");

		queryField.append(" select c.class_id as \"classId\", c.class_main_name as \"className\", c.class_num as \"classNum\", c.class_type as \"classTypeCode\", ");
		queryField.append("  c.class_user_num as \"classUserNum\", c.training_hour as \"trainingHour\", c.begin_date as \"beginDate\",c.end_date as \"endDate\", ");
		queryField.append("  c.enter_begin_time as \"enterBeginTime\", c.enter_end_time as \"enterEndTime\", c.training_dept_name as \"trainingDeptName\", c.open_state as \"openState\", ");
		queryField.append("  c.end_state as \"endState\",  (select cu.user_id from training_class_user cu where cu.class_id = c.class_id    and cu.active_state=:activeState and cu.auditing_state=:auditingState  and cu.user_id=:userId ) as \"studentId\", ");
		queryField.append(" (select count(cu.class_user_id)  from training_class_user cu  where cu.class_id = c.class_id and cu.active_state=:activeState and cu.auditing_state=:auditingState) as \"countUserNum\" ");				
		//				queryField.append(" from training_class c  left join training_class_user cu  on c.class_id = cu.class_id where 1=1 ");
		querySql1.append(" from training_class c  where 1=1  and c.class_type = :classType");
		querySql2.append(" from training_class c  left join training_class_user cu  on c.class_id = cu.class_id where 1=1  ");
		querySql2.append(" and cu.user_id =:userId");
		values.put("userId", queryCommond.getSearchUserId());

		values.put("auditingState", ClassUser.IS_AUDITING_Y);
		//				 专题班发布的
		querySql.append(" and c.publish_state =:publishState ");
		values.put("publishState", TrainingClass.IS_PUBLISH_Y);
		//				 专题班活动的
		querySql.append(" and c.active_state =:activeState ");
		values.put("activeState", TrainingClass.IS_ACTIVE_Y);
		querySql.append(" and c.audit_state =:auditState ");
		values.put("auditState", TrainingClass.IS_AUDIT_Y);
		//班级编号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			querySql.append(" and c.class_num = :classNum ");
			values.put("classNum", queryCommond.getSearchTrainingNum());
		}
		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			querySql.append(" and c.class_main_name like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			querySql.append(" and c.begin_date >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}						
		//培训机构名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())) {
			querySql.append(" and c.training_dept_name like :trainingDeptName  ");
			values.put("trainingDeptName","%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			querySql.append(" and c.Signup_State = :signUpState  ");
			values.put("signUpState",queryCommond.getSearchSignUpState());
		}	
		//拼写sql
		//有预授权
		if(queryCommond.getIsAuthorization()==TrainingClassQueryCommond.IS_AUTHORIZATION_Y){					
			countSql.append("(");
			countSql.append(countField);
			countSql.append(querySql1);
			countSql.append(querySql);
			countSql.append(" union ");
			countSql.append(countField);
			countSql.append(querySql2);
			countSql.append(querySql);
			countSql.append(")");

			sql.append(queryField);
			sql.append(querySql1);
			sql.append(querySql);
			sql.append(" union ");
			sql.append(queryField);
			sql.append(querySql2);
			sql.append(querySql);
			values.put("classType", queryCommond.getSearchClassType());
		}else if(queryCommond.getIsAuthorization()==TrainingClassQueryCommond.IS_AUTHORIZATION_N){				
			countSql.append("(");
			countSql.append(countField);
			countSql.append(querySql2);
			countSql.append(querySql);
			countSql.append(")");

			sql.append(queryField);
			sql.append(querySql2);
			sql.append(querySql);
		}									
		//查询总数
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(countSql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);			
		Long count=this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());

		//查询数量
		IConditionBuilder defaultQueryConditionBuilder1  = new DefaultQueryConditionBuilder();			
		defaultQueryConditionBuilder1.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder1.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder1.setResultObject(TrainingClassQueryBean.class);
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder1.buliderQueryCondition();
		queryCondition1.setParameters(values);
		//				Long count=this.count(queryCondition1);
		//				queryCommond.setCount(count);
		return (List<TrainingClassQueryBean>) this.findList(queryCondition1,firstResult,queryCommond.getLimit());
	}
	/**
	 * 单位管理员查询班级列表
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author YangJC
	 * @date 2016-01-06
	 */
	@Override
	public List<TrainingClassQueryBean> queryTrainingClassList(
			TrainingClassQueryCommond queryCommond) throws Exception {
		StringBuffer queryHql = new StringBuffer();
		//拼写查询的sql
		StringBuffer hql = new StringBuffer();
		hql.append("select t.classId as classId, t.trainingDeptName as trainingDeptName,t.reportState as reportState,t.auditState as auditState,t.classUserNum as classUserNum," +
				"t.openState as openState, t.className as className, t.classNum as classNum, t.selectedLetters as selectedLetters," +
				"t.beginDate as beginDate,t.endDate as endDate,t.trainingHour as trainingHour,t.signUpState as signUpState,");
		hql.append(" (select cp.asignCount from ClassPlanApply cp where cp.classId=t.classId and cp.deptId = :deptId ) as asignCount,");//调整人数
		hql.append(" (select nvl(sum(cp.planCount),0) from ClassPlanApply cp where cp.classId=t.classId and cp.deptId = :deptId ) as planCount,");//计划人数
		hql.append(" (select nvl(sum(cp.asignCount),0) from ClassPlanApply cp where cp.classId=t.classId and cp.deptId = :deptId ) as kbCount,");//可报名额 add by NingSS at 20160217
		hql.append(" (select count(cu.classUserId) from ClassUser cu,Department d,DepartmentUser du where cu.trainingClass.classId=t.classId and cu.user.userId = du.user.userId and du.department.deptID = d.deptID and d.deptID=:deptId ");
		if(!PropertyUtil.objectNotEmpty(queryCommond.getNotClassUserAuditState())) {
			hql.append(" and cu.auditingState in (1,5) ");
		}
		hql.append(" ) as reportedNum,");//已报人数,审核通过人数
		hql.append(" t.publishState as publishState,t.activeState as activeState,ct.dataCode as classTypeCode ,ct.dataName as classTypeStr,t.perfectState as perfectState,t.allotDeptState as allotDeptState,");
		hql.append("t.ministrieID as ministrieID,t.ministrieName as ministrieName ");//承办部委id和名称
		hql.append(" FROM TrainingClass t left join t.classType ct left join t.applyWay aw,ClassPlanApply cp where 1=1 and cp.classId=t.classId and cp.deptId = :deptId ");
		if(!PropertyUtil.objectNotEmpty(queryCommond.getIsAsignCount())) {
			hql.append(" and cp.asignCount>0 ");
		}
		hql.append(" and (CASE WHEN  cp.planCount is null THEN  0 ELSE cp.asignCount END) >0 ");		
		//查询数量的sql	
		StringBuffer countHql = new StringBuffer("FROM TrainingClass t left join t.classType ct,ClassPlanApply cp where 1=1 and cp.classId=t.classId and cp.deptId = :deptId ");
		if(!PropertyUtil.objectNotEmpty(queryCommond.getIsAsignCount())) {
			countHql.append(" and cp.asignCount>0 ");
		}
		countHql.append(" and (CASE WHEN  cp.planCount is null THEN  0 ELSE cp.asignCount END) >0 ");
		Object[] queryObj = buildQueryObject(queryCommond, queryHql);
		queryHql = (StringBuffer)queryObj[0];
		Map<String,Object> values = (Map<String,Object>)queryObj[1];
		values.put("deptId", queryCommond.getSearchTrainingApplyDeptId().trim());
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		countHql.append(queryHql);
		defaultQueryConditionBuilder.setBaseQueryString(countHql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		//
		// 数据列表查询
		hql.append(queryHql);
		//拼写排序的sql begin
		
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			hql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			hql.append(" order by t.createDate desc");
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		List<TrainingClassQueryBean> resultList=new ArrayList<TrainingClassQueryBean>();
		if(queryCommond.getLimit()==-1){
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition1);
		}else {
			int firstResult = queryCommond.calculate(count.longValue());
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition1,
					firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	/**
	 * 判断培训班编号是否一样
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @date 2016-1-15
	 */
	@Override
	public boolean decideClassNum(TrainingClassValidCommond vCommond) throws Exception {

		StringBuffer hql = new StringBuffer();
		Map<String, Object> values = new HashMap<String, Object>();

		hql.append("SELECT t FROM TrainingClass t where 1=1");
		if(PropertyUtil.objectNotEmpty(vCommond.getClassNum())) {
			hql.append(" and t.classNum=:classNum");
			values.put("classNum", vCommond.getClassNum());
		}
		if(PropertyUtil.objectNotEmpty(vCommond.getEntityID())) {
			hql.append(" and t.classId !=:classId");
			values.put("classId",vCommond.getEntityID());
		}
		hql.append(" and t.activeState=:activeState");
		values.put("activeState", TrainingClass.IS_ACTIVE_Y);

		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		List<?> list=this.executeFind(QueryStringType.HQL, hql.toString(), values, -1, -1);
		if(list!=null&&list.size()>0) {
			//表示有重复  不能进行添加
			return true;
		}
		return false;
	}

	/**
	 * @category 查询班级的报名情况(委托部委办班在线报名系统)
	 * @param validCommond
	 * @throws Exception 
	 * @author NingSS
	 * @return
	 */
	@Override
	public List<TrainingClassQueryBean> findClassUserAsignCount(
			TrainingClassQueryCommond queryCommond) throws Exception {
		Map<String,Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("select (select sum(decode(t.asign_count, null, 0, asign_count)) from training_class_plan_apply t " +
				"left join base_department d on t.dept_id = d.depid where d.treepath like :treepath and t.class_id =tc.class_id ) as \"asignCount\"," +
				"(select count(cu.class_user_id) from training_class_user cu where cu.class_id =tc.class_id and cu.auditing_state <> :auditingN ) as \"reportedCount\" from training_class tc where 1=1 ");
		if(PropertyUtil.objectNotEmpty(queryCommond.getTreePath())){
			params.put("treepath", "%"+queryCommond.getTreePath()+"%");
		}else{
			params.put("treepath", "");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			sql.append(" and tc.class_id = :classId");
			params.put("classId", queryCommond.getSearchClassId());
		}
		params.put("auditingN", ClassUser.IS_AUDITING_N);
		List<TrainingClassQueryBean> list=(List<TrainingClassQueryBean>)executeFind(QueryStringType.SQL, sql.toString(), params, TrainingClassQueryBean.class);
		return list;
	}	
	
	
	
	/**
	 * @评估查询
	  * @Name: findClassAssessmentt
	  * @Description: @param qCommond
	  * @Description: @param request
	  * @Description: @param response
	  * @Description: @param resultState
	  * @Description: @return
	  * @Description: @throws Exception
	  * @Author: GUOLEI
	  * @Version: V1.00
	  * @Create Date: 2016-2-6下午12:23:22
	  * @Parameters: ITrainingClassService
	  * @Return: String
	 */
	public List<TrainingClassQueryBean> findClassAssessmentt(TrainingClassQueryCommond queryCommond) throws Exception {
		
		List<TrainingClassQueryBean> trainingClassQueryList =null;
		Map<String, Object> values = new HashMap<String, Object>();
		//拼写查询的sql
		StringBuffer sql = new StringBuffer();
		sql.append("select classCount.classId as \"classId\",  classCount.createDate \"createDate\"," +
					"classCount.classNum as  \"classNum\"," +//培训班编号reportedNum
					"case when classCount.questionnaireName is null then classCount.className else concat(classCount.className,'-',classCount.questionnaireName) end \"className\"," +
					"classCount.sponsorName as  \"sponsorName\"," +//承办部委
					"classCount.sponsorID as \"sponsorID\",");//主办部委
					if(!(RoleConstants.ROLE_CODE_JW_ADMIN.equals(queryCommond.getSearchRoleCode())||RoleConstants.ROLE_CODE_GJC_ADMIN.equals(queryCommond.getSearchRoleCode()))){
						sql.append("classCount.csudId as \"ministrieID\",");//承办部委
					}
					sql.append("str_to_date(classCount.beginDate,'%Y-%m-%d %H:%i:%s') as  \"beginDate\"," +
					"str_to_date(classCount.endDate,'%Y-%m-%d %H:%i:%s') as  \"endDate\"," +
					"str_to_date(classCount.assessStartTime,'%Y-%m-%d %H:%i:%s') as \"assessStartTime\"," +
					"str_to_date(classCount.assessEndTime,'%Y-%m-%d %H:%i:%s') as  \"assessEndTime\"," +
					"classCount.planCount as  \"planCount\"," +
					"classCount.reportedNum as  \"reportedNum\"," +
					"classCount.answerUser as \"answerUser\", " +
					"classCount.questionnaireId as \"questionnaireId\", " +
					"ifnull ( classCount.questionnaireMethod, 1 ) AS \"questionnaireMethod\", "
					+ "classCount.allUser - (case classCount.questionnaireMethod when 2 then 0 else classCount.answerUser end) AS \"noAnswerUser\", "
					+ "case classCount.allUser when 0 then '0%' else ( concat(FORMAT( (case classCount.allUser when 0 then 0 else classCount.answerUser / classCount.allUser * 100 end),2 ), '%')) end as \"nswerPerc\" " +
				"from (select distinct tc.class_id as classId," +
						"tc.class_num as classNum, " +//培训班编号
						"tc.class_name as className," +
						"tc.questionnaire_method as questionnaireMethod," +
						"tc.sponsor_Name as sponsorName, " +//主板单位
						"tc.sponsor_Id as sponsorID, " +//主办ID
						"tc.BEGIN_DATE as beginDate," +
						"tc.publish_state as classPublishState," +
						"tc.end_date as endDate,");
						if(!(RoleConstants.ROLE_CODE_JW_ADMIN.equals(queryCommond.getSearchRoleCode())||RoleConstants.ROLE_CODE_GJC_ADMIN.equals(queryCommond.getSearchRoleCode()))){
							sql.append("csud.dept_id as csudId,");
						}
						sql.append("case when tc.assess_start_time is null then tc.end_date - 1 else  tc.assess_start_time end AS assessStartTime, "
								+ "case when tc.assess_end_time is null then tc.end_date + 2 else  tc.assess_end_time end AS assessEndTime,"+
						"tc.ministrie_id as ministrieId ," +
						"q.publish_state as publishState, " +
						"tc.active_state  as activeState," +
						"q.questionnaire_name as questionnaireName," +
						"q.questionnaire_id   as questionnaireId , " +
						"(select ifnull(sum(cp.plan_count), 0)from TRAINING_CLASS_PLAN_APPLY cp  where cp.class_id = tc.class_id) as planCount," +
						"(select count(cu.CLASS_USER_ID)from TRAINING_CLASS_USER cu where cu.class_id = tc.class_id and cu.auditing_state in (1, 5)) as reportedNum, " +
						"  (select count(distinct qua.USERID) from questionnaire_user_answer qua inner join training_class_user tcu on qua.USERID = tcu.user_id " +
						"where qua.QUESTIONNAIRE_ID =  q.questionnaire_id and qua.class_id=tc.class_id and tcu.class_id=tc.class_id "
						+ "and case tc.questionnaire_method when 1 then (tcu.auditing_state IN (1, 5)) else (tcu.auditing_state IN (1, 5, 6)) end ) as answerUser," +
//						" (case nvl(tc.questionnaire_method, 1) when 1 then (select count(distinct qua.USERID)  " +
//						"from questionnaire_user_answer qua inner join base_user bu on qua.USERID = bu.USERID where qua.QUESTIONNAIRE_ID = q.QUESTIONNAIRE_ID and bu.userid in " +
//						"(select tcu.user_id from training_class_user tcu where tcu.class_id = tc.class_id and tcu.auditing_state in (1, 5))) when 2 then (select count(distinct qua.USERID) " +
//						" from questionnaire_user_answer qua where qua.QUESTIONNAIRE_ID = q.QUESTIONNAIRE_ID  and qua.class_id = tc.class_id)  end) as answerUser," +
						"((select count(distinct tcu.class_user_id)  from training_class_user tcu  where tcu.class_id = tc.class_id   "
						+ "and case tc.questionnaire_method when 1 then (tcu.auditing_state IN (1, 5)) else (tcu.auditing_state IN (1, 5, 6)) end)) as allUser , " +
						"tc.create_date	as createDate " +
					"FROM training_class tc " +
						"left join QUESTIONNAIRE_SURVEY_OBJECT qso on qso.Survey_Object_Id = tc.class_id " +
						"left join questionnaire q on qso.questionnaire_id = q.questionnaire_id " +
//						"left join base_user bu on q.USERID = bu.USERID " +
					" left join CLASS_SCOPE_DEPARTMENT csud on tc.CLASS_ID = csud.class_id ) classCount  where 1=1 ");

		
		sql.append(" and classCount.publishState = :publishState ");
		sql.append(" and classCount.activeState = :activeState ");
		//试卷已发布
		values.put("publishState",QuestionnaireEntity.IS_PUBLISH_Y );
		//试卷已活动
		values.put("activeState", QuestionnaireEntity.ACTIVE_STATE_Y);
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			sql.append(" and classCount.classPublishState = :classPublishState ");
			values.put("classPublishState", queryCommond.getSearchPublishState());
		}
		
		//部委班级
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieID())){
			sql.append(" and classCount.ministrieId = :ministrieId ");
			values.put("ministrieId", queryCommond.getSearchMinistrieID());
		}
		
		//班级ID
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIds())) {
			sql.append(" and classCount.classId in (:ids) ");
			values.put("ids", queryCommond.getSearchClassIds());
		}
				
		//班级编号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassNum())) {
			sql.append(" and classCount.classNum like :classNum ");
			values.put("classNum", "%"+queryCommond.getSearchClassNum().trim()+"%");
		}
		// 专题班班级名称
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
			sql.append(" and classCount.className like :className ");
			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
		}
		
		//主办单位
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorName())) {
			sql.append(" and classCount.sponsorName like :sponsorName");
			values.put("sponsorName", "%"+queryCommond.getSearchSponsorName().trim()+"%");
		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			sql.append(" and classCount.beginDate >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			sql.append(" and classCount.endDate <=:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
		}
		//评估开始时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAssessStartTime())){
			sql.append(" and classCount.assessStartTime >= :assessStartTime");
			values.put("assessStartTime", DateUtil.getDateByString(queryCommond.getSearchAssessStartTime().trim(), "yyyy-MM-dd"));
		}
		//评估结束时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAssessEndTime())){
			sql.append(" and classCount.assessEndTime <= :assessEndTime");
			values.put("assessEndTime", DateUtil.getDateByString(queryCommond.getSearchAssessEndTime().trim(), "yyyy-MM-dd"));
		}
		
		if(RoleConstants.ROLE_CODE_JW_ADMIN.equals(queryCommond.getSearchRoleCode())||RoleConstants.ROLE_CODE_GJC_ADMIN.equals(queryCommond.getSearchRoleCode())){
			//XXX 教务管理员看到所有班级	
		}else{
			//主办单位id||承办单位ID
			sql.append(" and (classCount.sponsorId=:sponsorId or classCount.csudId=:sponsorId)  ");
		}
//		sql.append(" and (classCount.sponsorId=:sponsorId or classCount.csudId=:sponsorId)  ");
		values.put("sponsorId", queryCommond.getSearchSponsorId());
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingClassQueryBean.class);
		//查询班级数量
		/*countHql.append(queryHql);*/
		//拼写排序的sql begin
		SortInfoBean sortInfo = queryCommond.getSortInfos();
		if (sortInfo != null) {
			sql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			sql.append(" order by classCount.createDate desc");//modify by dhc @2014-04-14
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		//查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());

		if(queryCommond.getPageo() == -1){
			trainingClassQueryList=(List<TrainingClassQueryBean>) this.findList(queryCondition);
		}
		else{
			trainingClassQueryList=(List<TrainingClassQueryBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return trainingClassQueryList;
	}
	
	/**
	 * @评估人员
	  * @Name: findAnswerUser
	  * @Description: @param queryCommond
	  * @Description: @throws Exception
	  * @Author: RongSL
	  * @Version: V1.00
	  * @Create Date: 2016-2-16
	  * @Return: List<BaseUser>
	 */
	public List<BaseUserBean> findAnswerUser(TrainingClassQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		//拼写查询的sql
		StringBuffer sql = new StringBuffer();
		if(queryCommond.getAnswerUserStatus()==1){//查询已评估人员
			sql.append("select t.user_name as \"userName\"," +	//姓名
					"t.login_id as \"loginID\"," +	//登录ID
					"t.duty_remark as \"unit\"," +	//单位
					"t.data_name as \"rank\"," +	//职级
					"concat(t.userid,'') as \"userID\"," +//人员ID
					"t.order_num as \"rankOrderNum\"," +//职级排序
					"t.remark as \"remark\"," +//备注
					"t.mobile_telephone as \"mobilePhone\" " );	
			sql.append(" from (select b.user_name,b.login_id,b.duty_remark,tcu.remark,bb.data_name,bb.order_num,b.userid,tcu.mobile_telephone " +
					"from questionnaire_user_answer qua " +
					" inner join questionnaire q on qua.questionnaire_id = q.questionnaire_id " +
					"inner join QUESTIONNAIRE_SURVEY_OBJECT qso on qso.questionnaire_id = q.questionnaire_id " +
					"inner join training_class tc on tc.class_id = qso.survey_object_id " +
					"inner join "+SystemConstants.SYSTEM_CODE+"uum_user b  on qua.userid = b.userid " +
					" inner join base_basedata bb on b.duty_level_code= bb.data_code "
					+ " inner join training_class_user tcu on b.userid=tcu.user_Id and tcu.class_id = tc.class_id " +
					"where tc.class_id =:classId  and qua.class_id=tc.class_id " );
			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireId())){
				sql.append(" and q.questionnaire_id=:questionnaireId  ");
			}
			sql.append(" and b.userid in  ( select tcu.user_id  from training_class_user tcu  where tcu.class_id=tc.class_id and tcu.auditing_state in (1,5)  ) " );
			sql.append(" group by b.user_name,b.login_id,b.duty_remark,tcu.remark,bb.order_num,bb.data_name,b.userid,tcu.mobile_telephone) t ");
		}else{//查询未评估人员
			sql.append("select t.user_name        as \"userName\", " +
					"t.login_id         as \"loginID\", " +
					"t.duty_remark      as \"unit\", " +
					"t.data_name        as \"rank\", " +
					"concat(t.userid,'')           as \"userID\", " +
					"t.order_num        as \"rankOrderNum\", " +
					"t.remark           as \"remark\", " +
					"t.mobile_telephone as \"mobilePhone\" " +
					"from (select b.user_name, b.login_id, b.duty_remark, bb.data_name, b.userid, bb.order_num, tcu.remark, tcu.mobile_telephone " );	
			sql.append("from training_class tc " +
					" left join training_class_user tcu on tcu.class_id = tc.class_id " +
					" left join "+SystemConstants.SYSTEM_CODE+"uum_user b on tcu.user_id = b.userid " +
					" left join base_basedata bb on b.duty_level_code= bb.data_code " +
					"where tcu.user_id not in  (select distinct b.userid as userID " +
							" from questionnaire_user_answer qua " +
							" inner join questionnaire q  on qua.questionnaire_id = q.questionnaire_id " +
							" inner join "+SystemConstants.SYSTEM_CODE+"uum_user b   on qua.userid = b.userid " +
							"where  qua.class_id=tc.class_id and  b.userid in (select tcu.user_id  from training_class_user tcu " +
							"where tcu.class_id = qua.class_id   and tcu.auditing_state in (1, 5))  " );
							if(PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireId())){
								sql.append(" and q.questionnaire_id=:questionnaireId  ");
						}
			sql.append(" ) and tcu.auditing_state in (1, 5) " + "and tc.class_id =:classId ) t ");
		}
		//拼写排序的sql end
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			values.put("classId", queryCommond.getSearchClassId());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireId())){
			values.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		}
		
		//拼写排序的sql 
		SortInfoBean sortInfo = queryCommond.getSortInfos();
		if (sortInfo != null) {
			sql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			sql.append(" order by t.login_id ");
		}
				
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(BaseUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IQueryCondition queryCondition = defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		if(-1==queryCommond.getPage()){
			return (List<BaseUserBean>) this.findList(queryCondition);
		}else{
			Long count = this.count(queryCondition);
			int firstResult = queryCommond.calculate(count);
			return (List<BaseUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
	}
	
	/**
	 * 查询已评估人员
	 * @createDate 2017-3-2
	 * @author RongSL
	 * */
	@SuppressWarnings("deprecation")
	@Override
	public
	List<BaseUserBean> findAlreadyEvaluated(TrainingClassQueryCommond queryCommond) throws Exception {
		String sql=" select t.user_id \"userID\",t.user_name \"userName\",case t.qtype when 1 then t.login_id else concat(t.login_id,(@rowNum:=@rowNum+1)) end \"loginID\" " +
				"from (select distinct tcu.user_id,case tcu.auditing_state when 6 then 2 else 1 end qtype,tcu.user_name," +
				"case tcu.auditing_state when 6 then tcu.user_name else bu.login_id end login_id  from questionnaire_user_answer qua  " +
				"inner join training_class_user tcu on qua.USERID = tcu.user_id " +
				"left join base_user bu on tcu.user_id=bu.userid " +
				"left join training_class tc on tcu.class_id=tc.class_id " +
				"where qua.QUESTIONNAIRE_ID =? and qua.class_id = tcu.class_id " +
				"and tcu.class_id =? and (((tc.questionnaire_method = 1 or tc.questionnaire_method is null) " +
				"and tcu.auditing_state in (1, 5)) or (tc.questionnaire_method = 2 and tcu.auditing_state in (1, 5, 6)))) t, "
				+ "(Select (@rowNum :=0) ) b ";
		List<BaseUserBean> list=new ArrayList<BaseUserBean>();
		try {
			Connection conn = this.getSession().connection();
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1, queryCommond.getSearchQuestionnaireId());
			ps.setString(2, queryCommond.getSearchClassId());
			ResultSet rs =ps.executeQuery();
			BaseUserBean bub;
			while(rs.next()){
				bub=new BaseUserBean();
				bub.setLoginID(rs.getString("loginID"));
				bub.setUserID(rs.getString("userID"));
				bub.setUserName(rs.getString("userName"));
				list.add(bub);
			}
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
		}
		return list;
	}
	
	/**
	 * @评估人员(导出用)
	 * @Name: findAnswerUserExp
	 * @Description: @param queryCommond
	 * @Description: @throws Exception
	 * @Author: RongSL
	 * @Version: V1.00
	 * @Create Date: 2016-2-16
	 * @Return: List<BaseUser>
	 */
	@Override
	public List<BaseUserBean> findAnswerUserExp(TrainingClassQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		//拼写查询的sql
		StringBuffer sql = new StringBuffer();
		if(queryCommond.getAnswerUserStatus()==1){//查询已评估人员
			sql.append("select t.user_name as \"userName\"," +	//姓名
					"t.login_id as \"loginID\"," +	//登录ID
					"t.duty_remark as \"unit\"," +	//单位
					"t.data_name as \"rank\"," +	//职级
					"t.userid as \"userID\"," +//人员ID
					"t.order_num as \"rankOrderNum\"," +//职级排序
					"t.remark as \"remark\"," +//备注
					"t.mobile_telephone as \"mobilePhone\" " );	
			sql.append(" from (select b.user_name,b.login_id,b.duty_remark,b.remark,bb.data_name,bb.order_num,b.userid,b.mobile_telephone " +
					"from questionnaire_user_answer qua " +
					" inner join questionnaire q on qua.questionnaire_id = q.questionnaire_id " +
					"inner join QUESTIONNAIRE_SURVEY_OBJECT qso on qso.questionnaire_id = q.questionnaire_id " +
					"inner join training_class tc on tc.class_id = qso.survey_object_id " +
					"inner join base_user b  on qua.userid = b.userid " +
					" inner join base_basedata bb on b.duty_level_code = bb.data_code " +
					"where tc.class_id =:classId " );
			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireId())){
				sql.append(" and q.questionnaire_id=:questionnaireId  ");
			}
			sql.append(" and b.userid in  ( select tcu.user_id  from training_class_user tcu  where tcu.class_id=tc.class_id and tcu.auditing_state in (1,5)  ) " );
			sql.append(" group by b.user_name,b.login_id,b.duty_remark,b.remark,bb.order_num,bb.data_name,b.userid,b.mobile_telephone) t ");
		}else{//查询为评估人员
			sql.append("select t.user_name        as \"userName\", " +
					"t.login_id         as \"loginID\", " +
					"t.duty_remark      as \"unit\", " +
					"t.data_name        as \"rank\", " +
					"t.userid           as \"userID\", " +
					"t.order_num        as \"rankOrderNum\", " +
					"t.remark           as \"remark\", " +
					"t.mobile_telephone as \"mobilePhone\" " +
					"from (select b.user_name, b.login_id, b.duty_remark, bb.data_name, b.userid, bb.order_num, tcu.remark, b.mobile_telephone " );	
			sql.append("from training_class tc " +
					" left join training_class_user tcu on tcu.class_id = tc.class_id " +
					" left join base_user b on tcu.user_id = b.userid " +
					" left join base_basedata bb on b.duty_level_code = bb.data_code " +
					"where tcu.user_id not in  (select distinct b.userid as userID " +
					" from questionnaire_user_answer qua " +
					" inner join questionnaire q  on qua.questionnaire_id = q.questionnaire_id " +
					" inner join base_user b   on qua.userid = b.userid " +
					"where  b.userid in (select tcu.user_id  from training_class_user tcu " +
					"where tcu.class_id = tc.class_id   and tcu.auditing_state in (1, 5))  " );
			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireId())){
				sql.append(" and q.questionnaire_id=:questionnaireId  ");
			}
			sql.append(" ) and tcu.auditing_state in (1, 5) " + "and tc.class_id =:classId ) t ");
		}
		//拼写排序的sql end
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			values.put("classId", queryCommond.getSearchClassId());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireId())){
			values.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		}
		
		//拼写排序的sql 
		SortInfoBean sortInfo = queryCommond.getSortInfos();
		if (sortInfo != null) {
			sql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			sql.append(" order by t.login_id ");
		}
		
		IConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(BaseUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IQueryCondition queryCondition = defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		if(-1==queryCommond.getPage()){
			return (List<BaseUserBean>) this.findList(queryCondition);
		}else{
			Long count = this.count(queryCondition);
			int firstResult = queryCommond.calculate(count);
			return (List<BaseUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
	}

	/**
	 * 更新评估时间
	 * @param commond
	 * @throws Exception
	 * @author LongJZ
	 * @date 2016-4-18
	 */
	@Override
	public void updateAssessTime(TrainingClassValidCommond commond) throws Exception {
		String hql = "update TrainingClass tc set tc.assessStartTime = :startTime, tc.assessEndTime = :endTime where tc.classId = :classId";
		Map<String, Object> value = new HashMap<String, Object>();
		value.put("startTime", commond.getAssessStartTime());
		value.put("endTime", commond.getAssessEndTime());
		value.put("classId", commond.getClassId());
		this.executeUpdate(QueryStringType.HQL, hql, value);
	}
	
	
	/**
	 * 新增附件
	 * @param commond
	 * @throws Exception
	 * @author RongSL
	 * @date 2016-7-28
	 */
	public BaseAttachment saveBaseAttachment(BaseAttachment bd){
		this.save(bd);
		return bd;
	}
	
	/**
	 * 获得培训班考勤率
	 * @author RongSL
	 * @Date 2016-09-17
	 * */
	@Override
	public Map<String,Integer> getAttendanceChance(TrainingClassQueryCommond queryCommond){
//		String sql=" select count(1) \"attendance\",tca.class_id \"classId\" from training_class_attendance tca where tca.class_id in (:classIds) and tca.user_state=1  group by tca.class_id ";
		StringBuffer sql=new StringBuffer(" select (count(1)-nvl(ta.co,0)) \"attendance\", tcu.class_id \"classId\" from training_class_user tcu left join ( " +
				"select count(1) co,tca.class_id from training_class_attendance tca where tca.user_state=2 group by tca.class_id ) " +
				"ta on ta.class_id=tcu.class_id where 1=1  ");
		Map<String,Object> params=new HashMap<String,Object>();
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIds())){
			sql.append(" and tcu.class_id in (:classIds) ");
			params.put("classIds", queryCommond.getSearchClassIds());
		}
		sql.append(" group by tcu.class_id,ta.co ");
		List<TrainingClassQueryBean> list=(List<TrainingClassQueryBean>) this.executeFind(QueryStringType.SQL, sql.toString(), params,TrainingClassQueryBean.class);
		Map<String,Integer> ac=new HashMap<String,Integer>();
		for(TrainingClassQueryBean a:list){
			ac.put(a.getClassId(), a.getAttendance());
		}
		return ac;
	}
	
	/**
	 * @课程评估查询
	 * @Name: findClassAssessmentt
	 * @Author: RongSL
	 * @CreateDate: 2016-5-20
	 * @Return: List<TrainingClassQueryBean>
	 */
	@Override
	public List<TrainingClassQueryBean> findCourseAssessment(TrainingClassQueryCommond queryCommond) throws Exception {

		List<TrainingClassQueryBean> trainingClassQueryList = null;
		Map<String, Object> values = new HashMap<String, Object>();
		// 拼写查询的sql
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (select classCount.courseName as \"courseName\",classCount.courseNum as \"courseNum\", classCount.answerUser as \"answerUser\", "
				+ "classCount.allUser - ( CASE classCount.questionnaireMethod WHEN 2 THEN 0 ELSE classCount.answerUser END ) as \"noAnswerUser\",classCount.courseId as \"courseId\", "
				+ "CASE classCount.allUser WHEN 0 THEN '0%' ELSE ( concat( FORMAT( ( CASE classCount.allUser WHEN 0 THEN 0 ELSE classCount.answerUser / classCount.allUser * 100 END ), 2 ), '%' ) ) end as \"nswerPerc\", "
				+ "classCount.classId as \"classId\", CASE WHEN classCount.questionnaireName IS NULL THEN classCount.className "
				+ "ELSE concat( classCount.className, '-', classCount.questionnaireName ) end \"className\", classCount.beginDate as \"beginDate\", "
				+ "classCount.trainingDeptName as \"trainingDeptName\", classCount.endDate as \"endDate\",  ifnull(classCount.questionnaireMethod,1) as \"questionnaireMethod\","
				+ "classCount.assessStartTime as \"assessStartTime\", classCount.assessEndTime as \"assessEndTime\", "
				+ "classCount.questionnaireId as \"questionnaireId\" from (select tcc.class_course_id as courseId,concat(tcc.course_name,'-',q.questionnaire_name) as courseName, "
				+ "tcc.course_num as courseNum, tc.class_id as classId,tc.questionnaire_method as questionnaireMethod, tc. class_name as className,  tc.BEGIN_DATE as beginDate, tc.end_date as endDate, "
				+ "tc.training_dept_name as trainingDeptName,  ifnull( tc.assess_start_time, DATE_ADD(tc.end_date, INTERVAL - 1 DAY) ) as assessStartTime, "
				+ "ifnull( tc.assess_END_time, date_add(tc.end_date, INTERVAL 2 DAY) ) as assessEndTime, q.publish_state as publishState, tc.active_state as activeState, "
				+ "q.questionnaire_name as questionnaireName, q.questionnaire_id as questionnaireId,  (select count(distinct qua.USERID) from questionnaire_user_answer qua  " +
				"inner join training_class_user tcu on qua.USERID = tcu.USER_ID where qua.QUESTIONNAIRE_ID = q.QUESTIONNAIRE_ID and tcu.class_id=qua.class_id " +
				"and tcu.auditing_state in (1, 5,6)  and qua.class_id = tcc.class_course_id) as answerUser, "
				+ "(select count(distinct tcu.user_id) from training_class_user tcu where tcu.class_id = tc.class_id "
				+ "and tcu.auditing_state in (1, 5,6) and tcu.active_state=:activeState) as allUser,  tc.create_date as createDate FROM training_class tc left join training_class_course tcc on tcc.class_id = tc.class_id  "
				+ "left join QUESTIONNAIRE_SURVEY_OBJECT qso on qso.Survey_Object_Id = tcc.class_course_id left join questionnaire q on qso.questionnaire_id = q.questionnaire_id "
				+ "left join base_user bu on q.USERID = bu.USERID where tc.class_id =:classId and q.questionnaire_name is not null) classCount where 1 = 1 ");

		// 试卷已发布
		values.put("publishState", QuestionnaireEntity.IS_PUBLISH_Y);
		// 试卷已活动
		values.put("activeState", QuestionnaireEntity.ACTIVE_STATE_Y);
		// 班级ID
		values.put("classId", queryCommond.getSearchClassId());

		// 班级编号
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassNum())) {
			sql.append(" and classCount.classNum like :classNum ");
			values.put("classNum", "%" + queryCommond.getSearchClassNum().trim() + "%");
		}
		// 专题班班级名称
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())) {
			sql.append(" and classCount.className like :className ");
			values.put("className", "%" + queryCommond.getSearchClassName().trim() + "%");
		}

		// 承办部委
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieName())) {
			sql.append(" and classCount.ministrieName like :ministrieName");
			values.put("ministrieName", "%" + queryCommond.getSearchMinistrieName().trim() + "%");
		}
		// 开班时间
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			sql.append(" and classCount.beginDate >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		// 结班时间
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())) {
			sql.append(" and classCount.endDate <=:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
		}
		// 评估开始时间
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchAssessStartTime())) {
			sql.append(" and classCount.assessStartTime >= :assessStartTime");
			values.put("assessStartTime", DateUtil.getDateByString(queryCommond.getSearchAssessStartTime().trim(), "yyyy-MM-dd"));
		}
		// 评估结束时间
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchAssessEndTime())) {
			sql.append(" and classCount.assessEndTime <= :assessEndTime");
			values.put("assessEndTime", DateUtil.getDateByString(queryCommond.getSearchAssessEndTime().trim(), "yyyy-MM-dd"));
		}

		sql.append(" and classCount.publishState = :publishState ");
		sql.append(" and classCount.activeState = :activeState ) t");

		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(TrainingClassQueryBean.class);
		// 查询班级数量
		/* countHql.append(queryHql); */
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		//
		int firstResult = queryCommond.calculate(count.longValue());

		// 拼写排序的sql begin
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			if (sortInfo.getProperty().equals("t.reportedNum")) {
			} else {
				sql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			}
		} else {
			sql.append(" order by classCount.createDate desc");// modify by dhc
																// @2014-04-14
		}
		// 拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		if (queryCommond.getPageo() == -1) {
			trainingClassQueryList = (List<TrainingClassQueryBean>) this.findList(queryCondition);
		} else {
			trainingClassQueryList = (List<TrainingClassQueryBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return trainingClassQueryList;
	}
	
	/**
	 * @课程评估人员
	 * @Name: findCourseAnswerUser
	 * @Description: @param queryCommond
	 * @Description: @throws Exception
	 * @Author: RongSL
	 * @Version: V1.00
	 * @Create Date: 2016-5-30
	 * @Return: List<BaseUser>
	 */
	public List<BaseUserBean> findCourseAnswerUser(TrainingClassQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		// 拼写查询的sql
		StringBuffer sql = new StringBuffer();
		if (queryCommond.getAnswerUserStatus() == 1) {// 查询已评估人员
			sql.append("select t.user_name as \"userName\"," + // 姓名
					"t.login_id as \"loginID\"," + // 登录ID
//					"t.depname as \"unit\"," + // 单位
					"to_char(t.userid) as \"userID\"," + // 人员ID
//					"t.folkCode as \"folkCode\"," + // 人员ID
					"t.duty_remark as \"unit\", " + // 单位及职务
					"t.duty_level_code as \"rank\", " + // 职级
					"t.remark as \"remark\"," + // 备注
					"t.order_num as \"rankOrderNum\","+ // 人员ID
					"t.mobile_telephone as \"mobilePhone\" "); // 手机号
			sql.append(" from (select b.user_name,tcu.mobile_telephone,b.login_id,d.depname,b.userid,tcu.remark," 
//					"bd.data_name as folkCode," +
					+ "bd2.data_name as duty_level_code,bd2.order_num,b.duty_remark from questionnaire_user_answer qua "
					+ " inner join questionnaire q on qua.questionnaire_id = q.questionnaire_id "
					+ "inner join QUESTIONNAIRE_SURVEY_OBJECT qso on qso.questionnaire_id = q.questionnaire_id "
					+ "inner join training_class_course tcc on tcc.class_course_id = qso.survey_object_id inner join uum_user@uum_link b  on qua.userid = to_char(b.userid) "
					+ "inner join base_basedata bd  on to_char(b.duty_level_code)=bd.data_code "
					+ "inner join base_basedata bd2 on to_char(b.folk_code) = bd2.data_code "
					+ " inner join training_class_user tcu on to_char(b.userid)=tcu.user_Id and tcu.class_id = tcc.class_id " 
					+ "inner join base_department_user du  on to_char(b.userid)=du.userid inner join base_department d on du.depid=d.depid where tcc.class_course_id =:courseId and qua.class_id=tcc.class_course_id ");
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireId())) {
				sql.append(" and q.questionnaire_id=:questionnaireId  ");
			}
			sql.append(" and b.userid in  ( select tcu.user_id  from training_class_user tcu  where tcu.class_id=tcc.class_id and tcu.auditing_state in (1,5)  ) ");
			sql.append(" group by b.user_name,b.login_id,d.depname,b.userid,bd.data_name,tcu.remark,bd2.data_name,bd2.order_num,tcu.mobile_telephone,b.duty_remark order by b.login_id) t ");
		} else {// 查询未评估人员
			sql.append("select distinct b.user_name as \"userName\"," + // 姓名
					"b.login_id as \"loginID\"," + // 登录ID
//					"d.depname as \"unit\"," + // 单位
					"b.duty_remark as \"unit\"," + // 单位及职务
					"to_char(b.userid) as \"userID\"," + // 人员ID
					"tcu.remark as \"remark\"," + // 备注
					"bd.data_name as \"rank\"," + // 职级
					"tcu.mobile_telephone as \"mobilePhone\" "); // 手机号
			sql.append(" from training_class_course tcc left join training_class tc  on tcc.class_id=tc.class_id left join training_class_user tcu on tcu.class_id = tc.class_id "
					+ " left join uum_user@uum_link b on tcu.user_id = to_char(b.userid) left join base_department_user du  on to_char(b.userid)=du.userid " 
					+ "left join base_department d on du.depid=d.depid left join base_basedata bd on bd.data_code=to_char(b.duty_level_code) "
					+ "where tcu.user_id not in  (select distinct to_char(b.userid) as userID from questionnaire_user_answer qua "
					+ " inner join questionnaire q  on qua.questionnaire_id = q.questionnaire_id  inner join uum_user@uum_link b   on qua.userid = to_char(b.userid) "
					+ " inner join base_department_user du  on to_char(b.userid)=du.userid  inner join base_department d on du.depid=d.depid "
					+ "where  qua.class_id=tcc.class_course_id and b.userid in (select tcu.user_id  from training_class_user tcu where tcu.class_id = tc.class_id   and tcu.auditing_state in (1, 5))  ");
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireId())) {
				sql.append(" and q.questionnaire_id=:questionnaireId  ");
			}
			sql.append(" ) and tcu.auditing_state in (1, 5) " + "and tcc.class_course_id  =:courseId ");
			sql.append(" order by b.login_id ");
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchQuestionnaireId())) {
			values.put("questionnaireId", queryCommond.getSearchQuestionnaireId());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchCourseId())) {
			values.put("courseId", queryCommond.getSearchCourseId());
		}

		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		
		defaultQueryConditionBuilder.setResultObject(BaseUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IQueryCondition queryCondition = defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		if (-1 == queryCommond.getPage()) {
			return (List<BaseUserBean>) this.findList(queryCondition);
		} else {
			Long count = this.count(queryCondition);
			int firstResult = queryCommond.calculate(count);
			return (List<BaseUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
	}
	
	/**
	 * 查询子部门
	 * */
	public List<Department> findDedepartmentList(DepartmentValidCommond validCommond)  throws Exception{
		String hql=" from Department t where 1=1 ";
		Map<String,Object> params=new HashMap<String,Object>();
		if(PropertyUtil.objectNotEmpty(validCommond.getParentID())){
			hql+=" and t.parentID =:parentId ";
			params.put("parentId", validCommond.getParentID());
		}
		if(PropertyUtil.objectNotEmpty(validCommond.getTreepath())){
			hql+=" and t.treepath like :treePath ";
			params.put("treePath", validCommond.getTreepath()+"%");
		}
		if(PropertyUtil.objectNotEmpty(validCommond.getDeptIds())){
			hql+=" and t.deptID not in(:ids) ";
			params.put("ids", validCommond.getDeptIds());
		}
		hql+=DeptConstants.NOT_DEPID;
		List<Department> list=(List<Department>) this.executeFind(QueryStringType.HQL, hql, params);
		return list;
	}
	
	/**
	 * 查询子部门全路径
	 * */
	public String findDepartmentNamePath(DepartmentValidCommond validCommond)  throws Exception{
		StringBuffer sb=new StringBuffer(" select bd.depname from base_department bd where bd.depid in (:ids) order by instr(':ids',bd.depid) ");
		Map<String,Object> params=new HashMap<String,Object>();
		List list=this.executeFind(QueryStringType.SQL, sb.toString(), params);
		if(PropertyUtil.objectNotEmpty(list)&&PropertyUtil.objectNotEmpty(list.get(0))){
			StringBuffer s=new StringBuffer("");
			for(int i=0;i<list.size();i++){
				s.append(list.get(i).toString());
			}
			return s.toString();
		}
		return null;
	}
	
	/**
	 * 获得培训班编号
	 * @author RongSL
	 * @date 2017-01-07
	 * */
	public String getTrainingClassNum(){
		StringBuffer sb=new StringBuffer(" select max(t.class_num) from training_CLASS  t  ");
		Map<String,Object> params=new HashMap<String,Object>();
		List list=this.executeFind(QueryStringType.SQL, sb.toString(), params);
		if(PropertyUtil.objectNotEmpty(list)&&PropertyUtil.objectNotEmpty(list.get(0))){
			return list.get(0).toString();
		}
		return Calendar.getInstance().get(Calendar.YEAR)+"00000";
	}
	

	/**
	 * 保存实体bean
	 * @author RongSL
	 * @date 2017-01-08
	 * */
	public <T extends AbstractBaseEntity & IBaseEntity> void saveEntity(T view){
		super.save(view);
		this.getSession().flush();
	}
	
	/**
	 * 删除承办部委&指定报名机构
	 * @author RongSL
	 * @DATE 2017-01-08
	 * */
	public void deleteCBZD(TrainingClassValidCommond commond){
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("classId", commond.getClassId());
		String sql=" delete from CLASS_SCOPE_DEPARTMENT where class_id=:classId ";
		this.executeUpdate(QueryStringType.SQL, sql, params);
		sql=" delete from CLASS_SIGN_UP_DEPARTMENT where class_id=:classId ";
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	
	/**
	 * 通过LoginID查询User对象
	 */
	@Override
	public User findByLoginID(String loginID) {
		// TODO Auto-generated method stub
		String sql="from User where loginID=:loginID";
		Map<String,Object> params=new HashMap<String,Object>();
		if(PropertyUtil.objectNotEmpty(loginID)){
			params.put("loginID", loginID);
		}
		List list=this.executeFind(QueryStringType.HQL, sql, params);
		if(list!=null&&list.size()>0){
			return (User) list.get(0);
		}
		return null; 
	}
	/**
	 * 查询培训班用户关系表
	 */
	@Override
	public boolean findClassUser(TrainingClass tc, User user) {
		// TODO Auto-generated method stub
		String sql="select * from TRAINING_CLASS_USER tcu " +
				"left join training_class tc on tc.CLASS_ID=tcu.CLASS_ID " +
				"left join base_user bu on bu.USERID=tcu.USER_ID " +
				"where tcu.CLASS_ID=:classId and tcu.USER_ID=:userId";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("userId", user.getUserId());
		params.put("classId", tc.getClassId());
		List list=this.executeFind(QueryStringType.SQL, sql, params);
		if(PropertyUtil.objectNotEmpty(list)){
			return true;
		}else{
			return false;
		}
	}
	/**
	 * 新增用户关系表
	 */
	@Override
	public boolean saveClassUser(TrainingClass tc, User user) {
		// TODO Auto-generated method stub
		String sql="insert into ClassUser() values()";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("user", user);
		params.put("trainingClass", tc);
		int count=this.executeUpdate(QueryStringType.SQL, sql, params);
		if(count!=0){
			return true;
		}else{
			return false;
		}
	}

	@Override
	public TrainingClass findByClassId(String classId) {
		// TODO Auto-generated method stub
		String classNum=classId;
		String hql="from TrainingClass where classNum=:classNum";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("classNum", classNum);
		List list=this.executeFind(QueryStringType.HQL,hql,params);
		if(list.get(0)!=null){
			return (TrainingClass) list.get(0);
		}else{
			return null;
		}
	}

	@Override
	public int findRoleForScope(String deptId, String classNum) {
		// TODO Auto-generated method stub
		String sql="select count(1) from training_class tc " +
				"left join CLASS_SIGN_UP_DEPARTMENT csud on tc.class_id=csud.class_id " +
				"left join base_department bd on csud.dept_id=bd.depid " +
				"where tc.class_num=:classNum and csud.dept_id=:deptId ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("deptId", deptId);
		params.put("classNum", classNum);
		List list=this.executeFind(QueryStringType.SQL,sql,params);
		if(list.get(0)!=null){
			return Integer.valueOf(list.get(0).toString());
		}else{
			return 0;
		}
	}
	
	/**
	 * 查询是否有被市委组织部选为承办部委
	 * @author RongSL
	 * @Date 2017-01-19
	 * */
	public int isGJCChoiceUndertake(String depId){
		try{
			String sql=" select count(1) from training_class tc  left join class_scope_department csd on tc.class_id=csd.class_id where tc.sponsor_id=1 and tc.active_state =1 and csd.dept_id=:depId ";
			Map<String,Object> params=new HashMap<String,Object>();
			params.put("depId", depId);
			List<?> list=this.executeFind(QueryStringType.SQL, sql, params);
			return Integer.valueOf(list.get(0).toString());
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}
	}
	
	/**
	 * 批量更新问卷评估方式
	 * @author RongSl
	 * @Date 2017-02-13
	 * */
	@Override
	public void updateQuestionnaireMethod(TrainingClassQueryCommond queryCommond){
		String sql="update training_class tc set tc.questionnaire_Method=:questionnaireMethod where tc.class_id in (:classIds)";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("questionnaireMethod", queryCommond.getQuestionnaireMethod());
		params.put("classIds", queryCommond.getSearchClassIds());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
	
	//查询二级节点上的第一个一级节点 by RongSL 20170705
	public List<?> findAdminLevel(DepartmentTreeCondition condition) throws Exception{
		StringBuffer sb=new StringBuffer("select t.depid,t.treepath,t.depname from (select bd.depid,bd.depname,"
				+ "bd.treepath,bd.admin_level_code from base_department bd start with bd.depid = '"+condition.getNode()+"'  "
				+ "connect by prior bd.parent_id = bd.depid ) t where t.admin_level_code=1 and rownum=1  order by t.treepath");
		return this.executeFind(QueryStringType.SQL, sb.toString(), null);
	}
	
	@Override
	public List<TrainingClassQueryBean> findAuditTrainingClassList(TrainingClassQueryCommond queryCommond) throws Exception {
		String fromSql = "FROM "+SystemConstants.SYSTEM_CODE+"training_class_pxarchives t "
				+ "left join base_basedata b1 on t.class_type = b1.data_code, "
				+ SystemConstants.SYSTEM_CODE+"training_class_user_pxarchives c, "
				+SystemConstants.SYSTEM_CODE+"uum_department d, "
				+SystemConstants.SYSTEM_CODE+"uum_department_user u "
			    + "where t.class_id = c.class_id "
			    + "and c.user_id = u.userid "
			    + "and u.depid = d.depid " 
			    + "and t.active_state = 1 "
			    + "and d.treepath like '"+queryCommond.getTreePath()+"%' ";
		StringBuffer querySql = new StringBuffer("select distinct t.CLASS_ID as \"classId\", " 
				+ "t.CREATE_DATE as \"createDate\", "
				+ "ifnull ( t.ASSESS_START_TIME, date_add(t.END_DATE, interval -1 day) ) AS \"assessStartTime\", "
				+ "ifnull ( t.ASSESS_END_TIME, date_add(t.END_DATE, interval 2 day) ) AS \"assessEndTime\", "
				+ "t.TRAINING_DEPT_NAME as \"trainingDeptName\", " 
				+ "t.REPORT_STATE as \"reportState\", "
				+ "t.AUDIT_STATE as \"auditState\", " 
				+ "t.OPEN_STATE as \"openState\", "
				+ "t.CLASS_NAME as \"className\", " 
				+ "t.CREATE_DEPNAME as \"createDepname\", " 
				+ "t.BEGIN_DATE as \"beginDate\", "
				+ "t.END_DATE as \"endDate\", " 
				+ "t.TRAINING_HOUR as \"trainingHour\", "
				+ "t.SIGNUP_STATE as \"signUpState\", " 
				+ "t.LOCK_STATE as \"lockState\", " 
				+ "t.CATEGORY_ID as \"categoryId\", "
				+ "t.BEOFF_STATION as \"beoffStation\", " 
				+ "t.PUBLISH_STATE as \"publishState\", "
				+ "t.ACTIVE_STATE as \"activeState\", " 
				+ "b1.DATA_CODE as \"classTypeCode\", "
				+ "b1.DATA_NAME as \"classTypeStr\", " 
				+ "t.PERFECT_STATE as \"perfectState\", "
				+ "t.ALLOTDEPT_STATE as \"allotDeptState\", " 
				+ "t.SPONSOR_ID as \"sponsorID\", "
				+ "t.SPONSOR_NAME as \"sponsorName\", "
				+ "datediff(STR_TO_DATE ( date_format (now(), '%Y-%m-%d'), '%Y-%m-%d' ),t.enter_begin_time) AS \"startDateState\", "
				+ "datediff(STR_TO_DATE ( date_format (now(), '%Y-%m-%d'), '%Y-%m-%d' ) ,t.enter_end_time) \"endDateState\" ").append(fromSql);
//		StringBuffer countSql = new StringBuffer("select count(distinct t.class_id) as count ").append(fromSql);
		Object[] queryObj = buildQueryObjectBySql1(queryCommond, querySql);
		querySql = (StringBuffer)queryObj[0];
		Map<String,Object> values = (Map<String,Object>)queryObj[1];
		
		DefaultQueryConditionBuilder defaultQueryConditionBuilder  = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setResultObject(TrainingClassQueryBean.class);
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			if(sortInfo.getProperty().equals("t.trainingHour")){
				querySql.append(" order by  t.training_Hour "+PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
			}
//			querySql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		} else {
			querySql.append(" order by t.BEGIN_DATE ASC,t.CREATE_DATE desc,t.CLASS_ID ");
		}
		//拼写排序的sql end
		defaultQueryConditionBuilder.setBaseQueryString(querySql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<TrainingClassQueryBean> resultList=new ArrayList<TrainingClassQueryBean>();
		if(queryCommond.getLimit()==-1){
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition);
		}else {
//			Object c =this.executeFind(QueryStringType.SQL, countSql.toString(), null).get(0);
//			Long count = Long.valueOf(c.toString());
			int count=this.findList(queryCondition).size();
			int firstResult = queryCommond.calculate(count);
			resultList=(List<TrainingClassQueryBean>) this.findList(queryCondition,firstResult, queryCommond.getLimit());
		}
		return resultList;
	}
	
	@Override
	public List<TrainingClassQueryBean> findClassNum(TrainingClassQueryCommond commond) {
		Map<String,Object> para = new HashMap<String, Object>();
		/*reportedNum,");
		hql.append(" nvl(t.userNum,0) as reportedNumber,");
		hql.append(" nvl(t.shiguanNum,0) as shiguanNum*/
		String sql = " select tc.class_id as \"classId\", " + 
				" count(t.class_user_id) as \"reportedNumber\", " + 
				" ifnull((select count(t.class_user_id) " + 
				" from "+SystemConstants.SYSTEM_CODE+"training_class_user_pxarchives          t, " + 
				SystemConstants.SYSTEM_CODE+"uum_department       d, " + 
				SystemConstants.SYSTEM_CODE+"uum_department_user du " + 
				" where tc.class_id = t.class_id " + 
				" and t.user_id = du.userid " + 
				" and d.depid = du.depid " + 
				" and t.auditing_state = 3 " + 
				" and d.treepath like '"+commond.getTreePath()+"%' " + 
				" ),0), " + 
				" ifnull((select sum(t.TRAINING_HOURS) " + 
				" from "+SystemConstants.SYSTEM_CODE+"training_class_user_pxarchives          t, " + 
				SystemConstants.SYSTEM_CODE+"uum_department       d, " + 
				SystemConstants.SYSTEM_CODE+"uum_department_user du " + 
				" where tc.class_id = t.class_id " + 
				" and t.user_id = du.userid " + 
				" and d.depid = du.depid " + 
				" and t.auditing_state = 1 " + 
				" and d.treepath like '"+commond.getTreePath()+"%' " + 
				" ),0), " + 
				" ifnull((select FORMAT(avg(t.TRAINING_HOURS),2) "
				+ "from "+SystemConstants.SYSTEM_CODE+"training_class_user_pxarchives          t, " + 
				SystemConstants.SYSTEM_CODE+"uum_department       d, " + 
				SystemConstants.SYSTEM_CODE+"uum_department_user du " + 
				" where tc.class_id = t.class_id " + 
				" and t.user_id = du.userid " + 
				" and d.depid = du.depid " + 
				" and t.auditing_state in (1,2) " + 
				" and d.treepath like '"+commond.getTreePath()+"%' " + 
				" ),0),"
				+ "(select count(1)-count(t.class_user_id) FROM " + SystemConstants.SYSTEM_CODE +"uum_user u "
				+ "left join " + SystemConstants.SYSTEM_CODE +"uum_department_user du on u.USERID=du.userid left join " + SystemConstants.SYSTEM_CODE +"uum_department d on du.depid=d.depid "
				+ "left join " + SystemConstants.SYSTEM_CODE +"uum_sys_dep_sysrole_grp_view usds ON u.userid = usds.USERID WHERE usds.userid IS NULL  and d.treepath like '"+commond.getTreePath()+"%' ) " + 
				" from "+SystemConstants.SYSTEM_CODE+"training_class_pxarchives               tc, " + 
				SystemConstants.SYSTEM_CODE+"training_class_user_pxarchives          t, " + 
				SystemConstants.SYSTEM_CODE+"uum_department       d, " + 
				SystemConstants.SYSTEM_CODE+"uum_department_user du " + 
				" where tc.class_id = t.class_id " + 
				" and t.user_id = du.userid " + 
				" and d.depid = du.depid " + 
//				" and t.auditing_state in (1, 2, 3) " + 
				" and tc.class_id in ("+commond.getSearchClassId()+") " + 
				" and d.treepath like '"+commond.getTreePath()+"%' " + 
				" group by tc.class_id ";
//		if(PropertyUtil.objectNotEmpty(commond.getSearchClassId())){
//			sql+= "and tc.class_id in ("+commond.getSearchClassId()+") ";
//			//para.put("classId", commond.getSearchClassId());
//		}
//		if(PropertyUtil.objectNotEmpty(commond.getTreePath())){
//			sql+= "and d.treepath like '"+commond.getTreePath()+"%' ";
////			para.put("treepath", commond.getTreePath()+"%");
//		}
//        sql+= "group by t.class_id ";
        List<TrainingClassQueryBean> beanList = new ArrayList<TrainingClassQueryBean>();
        List<Object> list = (List<Object>) this.executeFind(QueryStringType.SQL, sql, para);
        for (int i = 0; i < list.size(); i++) {
        	TrainingClassQueryBean queryBean = new TrainingClassQueryBean();
        	Object[] obj = (Object[])list.get(i);
        	queryBean.setClassId(obj[0].toString());
        	BigInteger s = (BigInteger)obj[1];
        	BigInteger s2 = (BigInteger)obj[2];
//        	BigDecimal s3 = (BigDecimal)obj[3];
//        	BigDecimal s4 = (BigDecimal)obj[4];
//        	BigDecimal s5 = (BigDecimal)obj[5];
//        	queryBean.setReportedNumber(s.intValue());
//        	queryBean.setReportedNum(s2.intValue());
//        	queryBean.setShiguanNum(s3.intValue());
//        	queryBean.setAuditShiguanNum(s4.toString());
        	Integer s3 = s.intValue() - s2.intValue();
        	queryBean.setLockNum(s3.toString()+"/"+s2.toString());
        	queryBean.setTrainingClassSum(obj[3]);
        	queryBean.setTrainingClassAvg(obj[4]);
        	queryBean.setNoClassUserNum(obj[5]);
        	beanList.add(queryBean);
		}
		return beanList;
	}
	
	public Object[] buildQueryObjectBySql1(TrainingClassQueryCommond queryCommond,StringBuffer queryHql) throws Exception{
		//拼写查询条件sql begin
		Map<String, Object> values = new HashMap<String, Object>();

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserId())){
			queryHql.append(" and t.CLASS_ID in (select class_id from  "+SystemConstants.SYSTEM_CODE+"training_class_user_pxarchives tcu where tcu.USER_ID=:userId ) ");
			values.put("userId", queryCommond.getSearchUserId());
		}
		
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYearByUser())){
			queryHql.append(" and left(t.CLASS_NAME,4)=:yearByUser ");
			values.put("yearByUser", queryCommond.getSearchYearByUser());
		}
		
		//培训机构id查询
//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptId())){
//			queryHql.append(" and t.TRAINING_DEPT_ID like :trainingDeptId ");
//			values.put("trainingDeptId", queryCommond.getSearchTrainingDeptId().trim());
//		}
		//培训机构名称查询
//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingDeptName())){
//			queryHql.append(" and t.TRAINING_DEPT_NAME like :trainingDeptName ");
//			values.put("trainingDeptName", "%"+queryCommond.getSearchTrainingDeptName().trim()+"%");
//		}
		//班级类型
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			queryHql.append(" and t.CLASS_TYPE = :classType ");
			values.put("classType", queryCommond.getSearchClassType().trim());
		}
		
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYear())) {
			if (!"合计".equals(queryCommond.getSearchYear())) {
				queryHql.append( " and t.classname_year like  :hourYear");
				values.put("hourYear", queryCommond.getSearchYear().trim() + "%");
			}
		}
		
		//班级学号
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingNum())) {
			queryHql.append(" and t.class_Num like :classNum ");
			values.put("classNum", queryCommond.getSearchTrainingNum().trim());
		}

		// 专题班班级名称
//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())){
//			queryHql.append(" and t.class_Name like :className ");
//			values.put("className", "%"+queryCommond.getSearchClassName().trim()+"%");
//		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingHourMin())){
			queryHql.append(" and t.training_hour >=:trainingHourMin ");
			values.put("trainingHourMin",queryCommond.getSearchTrainingHourMin());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingHourMax())){
			queryHql.append(" and t.training_hour <=:trainingHourMax ");
			values.put("trainingHourMax",queryCommond.getSearchTrainingHourMax());
		}
		
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())) {
			queryCommond.setSearchClassName(queryCommond.getSearchClassName().replace(",", "，"));
			if (queryCommond.getSearchClassName().split("，").length > 1) {
				queryHql.append(" and (");
				for (String s : queryCommond.getSearchClassName().split("，")) {
					queryHql.append(" t.class_name like '" + SqlQueryUtil.changeString(s) + "' or");
				}
				queryHql = new StringBuffer(queryHql.substring(0, queryHql.length() - 2));
				queryHql.append(")");
			} else {
				queryHql.append(" and t.class_name like :className");
				values.put("className", SqlQueryUtil.changeString(queryCommond.getSearchClassName().replace(" ", "")));
			}
		}
		//项目上报状态
//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchReportState())) {
//			queryHql.append(" and t.REPORT_STATE = :reportState");
//			values.put("reportState", queryCommond.getSearchReportState());
//		}

		//项目审核状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchAuditState())){
			queryHql.append(" and t.audit_State = :auditState");
			values.put("auditState", queryCommond.getSearchAuditState());
		}
		//主办单位
//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName())) {
//			queryHql.append(" and t.sponsor_Name like :organizerName ");
//			values.put("organizerName", "%"+queryCommond.getSearchOrganizerName().trim()+"%");
//		}
		//开班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			queryHql.append(" and t.begin_Date >= :beginDate  ");
			values.put("beginDate", DateUtil.getDateByString(queryCommond.getSearchBeginDate().trim(), "yyyy-MM-dd"));
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.end_Date <=:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		//报名状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			queryHql.append(" and tu.auditing_State = :auditingState  ");
			values.put("auditingState",queryCommond.getSearchSignUpState());
		}
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndTime())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.end_Date <=:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndTime().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDateStr())){
			//queryHql.append(" and t.endDate between :endDate and :endDate1");
			queryHql.append(" and t.END_DATE =:endDate");
			values.put("endDate", DateUtil.getDateByString(queryCommond.getSearchEndDateStr().trim(), "yyyy-MM-dd"));
			//values.put("endDate1", DateUtil.getDateByString(queryCommond.getSearchEndDate().trim()+" 23:59:59", "yyyy-MM-dd HH:mm:ss"));
		}
		
		//结班时间
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNowDate())){
			queryHql.append(" and t.end_Date =:endDate ");
			values.put("endDate", new Date());
		}

		/*// 专题班开班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOpenState())){
			queryHql.append(" and t.openState =:openState ");
			values.put("openState", queryCommond.getSearchOpenState());
		}
		// 专题班结班状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())){
			queryHql.append(" and t.endState =:endState ");
			values.put("endState", queryCommond.getSearchEndState());
		}

		// 专题班发布状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPublishState())){
			queryHql.append(" and t.publishState =:publishState ");
			values.put("publishState", queryCommond.getSearchPublishState());
		}
		// 专题班活动状态
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())){
			queryHql.append(" and t.activeState =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}*/

		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassId())){
			queryHql.append(" and t.class_Id = :classId");
			values.put("classId", queryCommond.getSearchClassId().trim());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIds()))
		{
			queryHql.append(" and t.class_Id in (:classIds)");
			values.put("classIds", queryCommond.getSearchClassIds());
		}
		//部委选学报名系统字段
		//部委id
//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieID())) {
//			queryHql.append(" and t.MinisTRIE_ID = :ministrieId");
//			values.put("ministrieId", queryCommond.getSearchMinistrieID());
//		}
		//主办单位id||承办单位ID
//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorId())) {
//			queryHql.append(" and (t.sponsorID = :sponsorId or sd.dept.deptID=:sponsorId ) ");
//			values.put("sponsorId", queryCommond.getSearchSponsorId());
//		}
		//部委名称
//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieName())) {
//			queryHql.append(" and t.MinisTRIE_NAME like :ministrieName");
//			values.put("ministrieName","%"+queryCommond.getSearchMinistrieName()+"%");
//		}
		
		//主办单位查询
//		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorName())){
//			queryHql.append(" and t.sponsor_Name like :sponsorName");
//			values.put("sponsorName","%"+queryCommond.getSearchSponsorName()+"%");
//		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorName())) {
			queryCommond.setSearchSponsorName(queryCommond.getSearchSponsorName().replace(",", "，"));
			if (queryCommond.getSearchSponsorName().split("，").length > 1) {
				queryHql.append(" and (");
				for (String s : queryCommond.getSearchSponsorName().split("，")) {
					queryHql.append(" t.sponsor_Name like '" + SqlQueryUtil.changeString(s) + "' or");
				}
				queryHql = new StringBuffer(queryHql.substring(0, queryHql.length() - 2));
				queryHql.append(")");
			} else {
				queryHql.append(" and t.sponsor_Name like :sponsorName");
				values.put("sponsorName", SqlQueryUtil.changeString(queryCommond.getSearchSponsorName().replace(" ", "")));
			}
		}
		if(queryCommond.getSearchLockState()!=null){
			queryHql.append(" and t.lock_State = :lockState");
			values.put("lockState",queryCommond.getSearchLockState());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassTypeCode())){
			queryHql.append(" and t.CLASS_TYPE = :classType");
			values.put("classType",queryCommond.getSearchClassTypeCode());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYear())&&!queryCommond.getSearchYear().equals("-1")){
			queryHql.append(" and date_format(t.begin_Date,'%Y') = :year");
			values.put("year",queryCommond.getSearchYear().toString());
		}
		
		return new Object[]{queryHql,values};
	}
	
	/**
	 * 查询培训班(归档数据)
	 * @author RongSL
	 * 2017年12月22日
	 * */
	public TrainingClassValidCommond findTrainingPxarchives(TrainingClassValidCommond vCommond){
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sql=new StringBuffer("");
		sql.append(" SELECT t.CLASS_ID AS \"classId\", t.CLASS_NAME AS \"className\", left(t.CLASS_NAME,4) \"classnameYear\","
				+ "t.TRAINING_DEPT_NAME AS \"trainingDeptName\", t.CREATE_DEPNAME AS \"ministrieName\","
				+ "t.BEGIN_DATE AS \"beginDate\", t.END_DATE AS \"endDate\", b1.DATA_NAME AS \"classTypeStr\", "
				+ "t.TRAINING_HOUR AS \"trainingHour\", floor(t.TRAINING_HOUR/8) AS \"days\", "
				+ "t.SIGNUP_STATE AS \"signUpState\", t.LOCK_STATE AS \"lockState\", b2.data_name AS \"categoryId\", "
				+ "t.BEOFF_STATION AS \"beoffStation\", t.PUBLISH_STATE AS \"publishState\", t.ACTIVE_STATE AS \"activeState\", "
				+ "t.PERFECT_STATE AS \"perfectState\", t.ALLOTDEPT_STATE AS \"allotDeptState\", "
				+ "t.SPONSOR_ID AS \"sponsorID\", t.SPONSOR_NAME AS \"sponsorName\", b3.data_name \"trainingDeptId\","
				+ "t.TRAINING_DEPT_NAME \"trainingDeptName\" FROM "+SystemConstants.SYSTEM_CODE+"training_class_pxarchives t "
				+ "LEFT JOIN base_basedata b1 ON t.class_type = b1.data_code LEFT JOIN base_basedata b2 ON t.CATEGORY_ID = b2.data_code "
				+ "LEFT JOIN base_basedata b3 ON t.TRAINING_DEPT_ID = b3.data_code WHERE 1=1 ");
		if(PropertyUtil.objectNotEmpty(vCommond.getClassId())){
			sql.append(" and t.class_id=:classId ");
			params.put("classId", vCommond.getClassId());
		}
		sql.append(" and t.active_state = 1 ORDER BY t.BEGIN_DATE ASC,t.CREATE_DATE desc,t.CLASS_ID ");
		return (TrainingClassValidCommond) this.executeFind(QueryStringType.SQL, sql.toString(), params,TrainingClassValidCommond.class).get(0);
	}
}
